PostgreSQL – Comparing Two Values When One or More Could Be NULL

nullpostgresql

I am writing a trigger and want to see if one column has changed could be NULL and trying to avoid the following:

IF     NEW.value != OLD.value 
    OR (NEW.value IS NULL AND OLD.value IS NOT NULL) 
    OR (NEW.value IS NOT NULL AND OLD.value IS NULL) THEN
...
END IF;

This is postgres 9.5 in a function (stored procedure)

Best Answer

Equivalent and simpler:

IF  NEW.value IS DISTINCT FROM OLD.value  THEN
...
END IF;

Related Solutions

Sql-server – Comparing Columns that can contain NULLS – is there a more elegant way

You can use this approach from Paul White's article Undocumented Query Plans: Equality Comparisons

SELECT * 
FROM   A 
       INNER JOIN B 
         ON A.ID = B.ID 
            AND EXISTS(SELECT A.* 
                       EXCEPT 
                       SELECT B.*)

Sql-server – Easier way to handle so many isnull() situation

No, there is no way to tell SQL Server to treat all NULL float values as zero. You will have to surround these expressions with ISNULL() or, better yet IMHO, COALESCE(). You can do this in a view so you don't have to repeat it in every query.

Best Answer

Related Solutions

Sql-server – Comparing Columns that can contain NULLS – is there a more elegant way

Sql-server – Easier way to handle so many isnull() situation

Related Question