PostgreSQL comparing null values in case statement

Question

when I wrote a case statement to compare values in tables, it came unstuck with variables that are null. It thinks they are different (note: col1 is a character field).

select a.id,
       a.col1 as a_col1,
       b.col1 as b.col1,
       case when a.col1=b.col1 then 0 else 1 end as chk_col1
from   tablea a,
       tableb b
where a.id=b.id;

... chk_col1 is always 0 when both col1's are null. I've tried

coalesce(a.col1,'null') as coalesce(b.col1,'null')

but this didn't work either. It still returned 1 for chk_col1.

Gordon Linoff · Accepted Answer · 2020-02-14 11:48:06Z

2

Postgres supports the null-safe comparison operator is not distinct from. So, try this:

select a.id,
       a.col1 as a_col1,
       b.col1 as b.col1,
       (case when a.col1 is not distinct from b.col1 then 0 else 1 end) as chk_col1
from tablea a join
     tableb b
     on a.id = b.id;

Personally, I would leave the value as a boolean:

select a.id, a.col1 as a_col1, b.col1 as b.col1,
       (a.col1 is distinct from b.col1) as chk_col1
from tablea a join
     tableb b
     on a.id = b.id;

Also note that I used proper, explicit, standard, readable JOIN syntax.

answered Feb 14, 2020 at 11:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Angus · Accepted Answer · 2020-02-14 11:46:45Z

0

SOLUTION! : The variable referenced in the colaesce function must be the calculated one, i.e.

coalesce(a_col1,'null') as coalesce(b_col1,'null')

Another thing I discovered. Let's say col2 is numeric. The above doesn't work, you'd need to use a 0. Or ... more cunningly, you can use '0', i.e.

coalesce(a_col2,'0') as coalesce(b_col2,'0')

This is handy to know if you want to generate some code to compare tables by referencing pg_tables or svv_columns. In this code I had 2 tables that I'd created by reading svv_columns metadata table, and I wanted to created a case statement for each variable, so I'd have the two variables from each table side by side plus a check variable which I'd use for summarising later:

select '       coalesce(a.'||a.column_name||',''0'') as a_'||a.column_name||', coalesce(b.'||b.column_name||',''0'') as b_'||b.column_name||', case when a_'||a.column_name||'=b_'||b.column_name||' then 0 else 1 end as chk_'||a.column_name||','
from   tbl_a_vars a,
       tbl_b_vars b
where a.column_name=b.column_name;

answered Feb 14, 2020 at 11:46

Angus

1671 silver badge15 bronze badges

1 Comment

Angus Over a year ago

Note: the above doesn't work for date/time fields unfortunately.

Collectives™ on Stack Overflow

PostgreSQL comparing null values in case statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related