01 June 2022

TIL Postgres NULL comparisons rules can bite you on WHERE … NOT IN

TIL in Postgres, if you do this:

SELECT * FROM things WHERE property NOT IN ('foo', 'bar', 'baz');

…it won’t take rows where property was NULL.

Surprising, but under the hood, it’s trying to do an equality comparison, which never works on NULL.

Further discussion on StackOverflow