Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A PostgreSQL query drops rows with NULL when you use = NULL, <> or NOT LIKE

ErrorSELECT * 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.

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;
  2. 2Match what you see, then jump to its fix

    1. 1WHERE col = NULL returns no rows, even though NULL values exist= NULL and <> NULL are never trueFix
    2. 2WHERE 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. 3Code written for another database works there but returns nothing hereAn old application relies on col = NULL meaning IS NULLFix

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 NULL
  • WHERE 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.