PostgreSQL Update Column based on Multiple Conditions

Question

Setting:

I use PostgreSQL 9.1.2 and have a basic table as below, where I have 4 binary columns that can take on the values Y or N. These columns also largely contain Null values, which I have denoted as '-' in the table below:

    Binary Col 1   Binary Col 2   Binary Col 3   Binary Col 4   Summary Col
    ------------   ------------   ------------   ------------   -----------
1.       Y             N               -             N               1
2.       -             Y               N             Y               2
3.       N             N               -             N               0
4.       -             -               -             -               -
5.       Y             Y               Y             Y               4

Problem:

I want to include a Summary Column to my table which is filled based on the number of 'Y's that occur in these 4 binary columns. I gave 5 example entries in the table above to showcase the desired output. One important thing to note is that I want to be able to differentiate cases like entry 3 (no 'Y's observed) from cases like entry 4 (all binary columns are null). This is a one time effort and I do not have performance concerns as to how this duplicative column would affect the transactional speed of my table.

I looked into quite a few PostgreSQL "Update table" examples and also checked the manual here. However, I could not find an example of an update procedure with multiple conditions yet, which I believe is what is required here.

Or may be I am completely wrong and the solution requires an SQL function or a trigger, any tips or suggestions?

What do you mean by binary column? What is its type?

Clodoaldo Neto
– Clodoaldo Neto

2013-03-26 16:58:24 +00:00
Commented Mar 26, 2013 at 16:58 — Clodoaldo Neto
– Clodoaldo Neto, Commented Mar 26, 2013 at 16:58

Clodoaldo Neto · Accepted Answer · 2013-03-26 17:09:54Z

5

SQL Fiddle

select
    c1, c2, c3, c4,
        (coalesce(c1, '') = 'Y')::integer
        + (coalesce(c2, '') = 'Y')::integer
        + (coalesce(c3, '') = 'Y')::integer
        + (coalesce(c4, '') = 'Y')::integer
    total_Y,
        (c1 is null)::integer
        + (c2 is null)::integer
        + (c3 is null)::integer
        + (c4 is null)::integer
    total_null
from t

answered Mar 26, 2013 at 17:09

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zhubarb Over a year ago

Thank you, I added 'coalesce' to the question's tags.

Ants Aasma · Accepted Answer · 2013-03-26 17:12:07Z

2

As a happy coincidence, the sum function has exactly the behavior you are looking for. You just need to convert the values into rows:

UPDATE some_table SET summary = (SELECT SUM(v::int) FROM (VALUES (col1),(col2),(col3),(col4)) as x(v));

answered Mar 26, 2013 at 17:12

Ants Aasma

55.3k16 gold badges98 silver badges99 bronze badges

2 Comments

harry Over a year ago

Can you kindly explain the logic?

Ants Aasma Over a year ago

The 4 columns are expanded as a single column relation with 4 rows, the boolean values are converted to int, false becoming 0 and true becoming 1, the values are then summed to return the desired result. This relies on the behavior of SQL sum aggregate that is defined to skip nulls and return null when all aggregated values are null.

Collectives™ on Stack Overflow

PostgreSQL Update Column based on Multiple Conditions

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related