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?
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