I know can't compare columns that are empty/null to others so often times I find myself writing a query like:
WHERE field2 <> '' AND coalesce(field1, '') <> coalesce(field2, '')
NULL <comparison operator> anything
No. The use of
COALESCE() generally prevents indexes from being used. The equivalent syntax using boolean logic gets cumbersome.
The best solution is to use
IS DISTINCT FROM:
where field1 is distinct from field2
By using built-in operators, there is a better change that Postgres can optimize the query.
IS DISTINCT FROM is ANSI standard syntax which is explained in the documentation.