Treat null as if it is a known value #postgresql
When you query a table sometimes you want to check if a nullable field is not equal to a value. For example:
select * from sometable where afield != 'avalue';
However the query above will exclude rows where
afield is null, so you would typically add that as an additional condition:
select * from sometable where afield is null or afield != 'avalue';
When you are doing it once it may be ok but as queries get bigger this makes the query messy and harder to read. Fortunately Postgres offers a more idiomatic way to check if a value does not equal something, including null values:
is distinct from and
is not distinct from.
select * from sometable where afield is distinct from 'avalue';
This query will return all the rows where
afield is null or anything but
select * from sometable where afield is NOT distinct from (select x from y limit 1);
will return all the values that are equal to the result of the subquery above and is useful when the result of the subquery could be null.
h/t Jack Christensen
Original docs: https://wiki.postgresql.org/wiki/Is_distinct_fromTweet