POSTGRESQL unable to compare null columns

Question

Following is my SQL query:

update table 
set unit_id = T1.unit_id_temp from
       (select case when unit_id4 is not null then unit_id4
        when unit_id3 is not null then unit_id3
        when unit_id2 is not null then unit_id2
        when unit_id1 is not null then unit_id1
        else unit_id
        end as unit_id_temp,
        unit_id1,unit_id2,unit_id3,unit_id4
        from   table)  T1
where table.unit_id4  = T1.unit_id4
and   table.unit_id3  = T1.unit_id3
and   table.unit_id2  = T1.unit_id2  
and   table.unit_id1  = T1.unit_id1

From the above query i am unable to compare columns when they are NULL, as the condition returns false.

For Example: when unit_id4 is null , table.unit_id4 = T1.unit_id4 returns False.

Hence my update fails whenever either of the columns contain NULL vallues.

I tried using is not distinct from, it does not work. Throws the following error:

UPDATE - 0 rows, 0.000 secs] [Code: 0, SQL State: 42601] ERROR: syntax error at or near "distinct"

Tim Biegeleisen · Accepted Answer · 2016-07-25 07:41:01Z

3

I think you can replace your entire query with this:

UPDATE table
SET unit_id = COALESCE(unit_id4, unit_id3, unit_id2, unit_id1)
WHERE COALESCE(unit_id4, unit_id3, unit_id2, unit_id1) IS NOT NULL

It seems to me that your logic is to update the unit_id column to unit_id4, unit_id3, unit_id2, unit_id1, if any of these columns be not NULL, in this order. The COALESCE function works well for this use case. In the event that all four columns are NULL, the logic in your UPDATE is to simply assign the same value back to unit_id. The WHERE clause in my query will prevent the UPDATE from even running on records where all four unit columns be NULL, so there will never be any wasted effort.

edited Jul 25, 2016 at 7:41

answered Jul 25, 2016 at 7:22

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

3 Comments

Renukadevi Over a year ago

Okay., I am so sorry I missed some information: I am trying this on AWS Redshift. I tried the previous query already and what I got was ": Target table must be part of an equijoin predicate"

Tim Biegeleisen Over a year ago

I would drop your original query if I were you, because I think it can be rewritten more simply.

Renukadevi Over a year ago

urghhhh., thanks a lot.. that makes me dumb.. It dint snap my mind to use COALESCE.

Collectives™ on Stack Overflow

POSTGRESQL unable to compare null columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related