2

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)

4

Equivalent and simpler:

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

Alternately:

IF COALESCE(New.Value, -1) <> COALESCE(Old.Value, -1) THEN ...
END IF;

assuming that you know -1 won't appear as a value.

  • Actually i cant rule out -1 as a possible input. – Frank Conry Nov 3 '16 at 3:12
  • In which case IS DISTINCT FROM is probably safer. Lucky Postgres users... – mendosi Nov 3 '16 at 3:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.