A PostgreSQL query drops rows with NULL when you use = NULL, <> or NOT LIKE
SELECT * FROM t WHERE col = NULL returns (0 rows)Short answer
In PostgreSQL a comparison with NULL gives NULL, not true or false, and WHERE keeps only rows that give true. So = NULL matches nothing, and col NOT LIKE 'x%' silently skips rows where col is NULL. Use IS NULL, IS DISTINCT FROM, or add an explicit OR col IS NULL.
Fix it fast
1Find out which cause you have
Ask PostgreSQL what the comparison returns for a NULL value. A result of NULL, shown as an empty cell, is the cause.
SELECT NULL = NULL AS eq, NULL <> 'a' AS ne, NOT (NULL IS NULL) AS n;2Match what you see, then jump to its fix
- 1
WHERE col = NULL returns no rows, even though NULL values exist= NULL and <> NULL are never trueFix - 2
WHERE col <> 'a' or WHERE col NOT LIKE 'a%' returns fewer rows than expected, none of them NULL<> or NOT LIKE skips the rows whose column is NULLFix - 3
Code written for another database works there but returns nothing hereAn old application relies on col = NULL meaning IS NULLFix
- 1
What does the error look like?
SELECT * FROM t WHERE col = NULL returns (0 rows)WHERE col <> 'a' does not return rows where col is NULLWHERE name NOT LIKE 'A%' misses rows whose name is NULL
Cause 1 of 3
= NULL and <> NULL are never true
1Check
Ordinary comparison operators give NULL when either side is NULL, and a row stays only when the condition is true.
SELECT 7 = NULL AS a, 7 <> NULL AS b;2Fix
Test for NULL with IS NULL or IS NOT NULL.
SELECT * FROM t WHERE col IS NULL;
SELECT * FROM t WHERE col IS NOT NULL;3Verify
The query now returns the rows where col is empty.
SELECT count(*) FROM t WHERE col IS NULL;Confirmed by postgresql.org
Cause 2 of 3
<> or NOT LIKE skips the rows whose column is NULL
1Check
NOT of an unknown value is still unknown, so the row is dropped. NOT LIKE is documented as the same as NOT (string LIKE pattern).
SELECT count(*) FROM t;
SELECT count(*) FROM t WHERE col NOT LIKE 'a%';
SELECT count(*) FROM t WHERE col IS NULL;2Fix
Say what you want for NULL. IS DISTINCT FROM treats NULL as an ordinary value; for NOT LIKE add the NULL case or replace NULL with an empty string.
SELECT * FROM t WHERE col IS DISTINCT FROM 'a';
SELECT * FROM t WHERE col NOT LIKE 'a%' OR col IS NULL;
SELECT * FROM t WHERE COALESCE(col, '') NOT LIKE 'a%';3Verify
The first two counts above, added to the NULL count, now equal the table count.
SELECT count(*) FROM t WHERE col NOT LIKE 'a%' OR col IS NULL;Confirmed by postgresql.org
Cause 3 of 3
An old application relies on col = NULL meaning IS NULL
1Check
PostgreSQL follows the SQL standard. A setting called transform_null_equals exists only as a compatibility switch.
SHOW transform_null_equals;2Fix
Change the queries to IS NULL. The documentation recommends this over turning the compatibility setting on.
SELECT * FROM t WHERE col IS NULL;3Verify
The rewritten query returns the NULL rows with the setting left off.
SHOW transform_null_equals;Confirmed by postgresql.org
How do I stop it coming back?
- Make columns NOT NULL with a default when an empty value has no meaning.
- Write NULL handling into every filter on a nullable column, and read each condition as 'true, false or unknown'.
- Compare two columns that may be NULL with IS NOT DISTINCT FROM.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
A PostgreSQL query with NOT IN returns nothing when the subquery contains NULL
The same query returns the right rows after you delete NULLs from the subquery table
If the subquery behind NOT IN returns even one NULL, the result is NULL instead of true for every row that has no match, so the WHERE clause keeps nothing.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix