CASE WHEN does not match NULL values in PostgreSQL
CASE WHEN col = NULL THEN ... never firesShort answer
A CASE branch runs only when its condition is true. A comparison with NULL gives null, not true, so the row skips that branch. Replace = NULL and CASE col WHEN NULL with a searched CASE using IS NULL, and use IS DISTINCT FROM when a NULL should count as different.
Fix it fast
1Find out which cause you have
Run the comparison on its own. If it shows null instead of true or false, the CASE will treat it as not true.
SELECT 7 = NULL AS eq, 7 <> NULL AS ne, 7 IS NULL AS is_null;2Match what you see, then jump to its fix
- 1
The NULL branch is never taken, whatever the dataThe condition is written as = NULL, or as the simple form CASE col WHEN NULLFix - 2
WHEN status <> 'paid' skips rows where status is NULL, so they go to ELSEA not-equal test on a column that holds NULLFix - 3
The column shows NULL where you expected a default labelNo ELSE, so unmatched rows come out as NULLFix
- 1
What does the error look like?
CASE WHEN col = NULL THEN ... never firesRows with NULL land in ELSECASE returns NULL when no WHEN matches and there is no ELSE
Cause 1 of 3
The condition is written as = NULL, or as the simple form CASE col WHEN NULL
1Check
The comparison page says ordinary operators return null when an input is null and says not to write expression = NULL. The CASE page says a branch runs only if its condition is true, and the simple form compares the value for equality.
SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'not matched' END;2Fix
Use a searched CASE with IS NULL.
SELECT CASE WHEN col IS NULL THEN 'missing' ELSE 'present' END FROM test;3Verify
The earlier check shows 'not matched'; the IS NULL version shows 'missing' for NULL rows.
SELECT CASE WHEN NULL IS NULL THEN 'missing' ELSE 'present' END;Confirmed by postgresql.org
Cause 2 of 3
A not-equal test on a column that holds NULL
1Check
status <> 'paid' is null when status is NULL, which is not true, so the row moves to the next WHEN or to ELSE.
SELECT NULL <> 'paid' AS result;2Fix
Use IS DISTINCT FROM, which treats NULL as an ordinary value.
SELECT CASE WHEN status IS DISTINCT FROM 'paid' THEN 'unpaid' ELSE 'paid' END FROM orders;3Verify
Rows with a NULL status now return 'unpaid'.
SELECT NULL IS DISTINCT FROM 'paid' AS result;Confirmed by postgresql.org
Cause 3 of 3
No ELSE, so unmatched rows come out as NULL
1Check
The CASE page says that if ELSE is omitted and no condition is true, the result is null.
SELECT CASE WHEN 1 = 2 THEN 'a' END AS result;2Fix
Add an ELSE with the value you want, or wrap the result in COALESCE.
SELECT CASE WHEN score > 50 THEN 'pass' ELSE 'fail' END FROM results;3Verify
The result column has no unexpected NULLs.
SELECT count(*) FROM (SELECT CASE WHEN score > 50 THEN 'pass' ELSE 'fail' END AS r FROM results) t WHERE r IS NULL;Confirmed by postgresql.org
How do I stop it coming back?
- Treat NULL as 'unknown' and write the NULL case explicitly with IS NULL.
- Always add an ELSE so the missing case is a visible choice.
- Use IS DISTINCT FROM and IS NOT DISTINCT FROM for null-safe comparisons.
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
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 fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fix