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)

2 Answers 2

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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