Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

CASE WHEN does not match NULL values in PostgreSQL

ErrorCASE WHEN col = NULL THEN ... never fires

Short 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.

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

Fix it fast

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

    1. 1The NULL branch is never taken, whatever the dataThe condition is written as = NULL, or as the simple form CASE col WHEN NULLFix
    2. 2WHEN status <> 'paid' skips rows where status is NULL, so they go to ELSEA not-equal test on a column that holds NULLFix
    3. 3The column shows NULL where you expected a default labelNo ELSE, so unmatched rows come out as NULLFix

What does the error look like?

  • CASE WHEN col = NULL THEN ... never fires
  • Rows with NULL land in ELSE
  • CASE 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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

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