Cookbook recipe

How NULLs behave in comparisons and indexes

Applies to PostgreSQL 13–17 Last reviewed May 2026 Grounded in source

What This Is

A production experience capsule based on real incident patterns.

Why It Matters

Builds production judgment under pressure.

Next Step

Run the decision point → · Enter full simulator →

Estimated investigation4 min

Scenario

A WHERE clause silently drops rows, or a UNIQUE constraint allows duplicate NULLs. NULL has its own three-valued logic. Diagnose it NULL comparisons return NULL, not true/false: SELECT NULL = NULL; -- NULL SELECT NULL IS…

Investigation Path

A WHERE clause silently drops rows, or a UNIQUE constraint allows duplicate NULLs. NULL has its own three-valued logic.

Diagnose it

NULL comparisons return NULL, not true/false:

SELECT NULL = NULL;     -- NULL
SELECT NULL IS NULL;    -- true
SELECT 1 WHERE NULL;    -- no row (NULL is not true)

Why it happens

SQL uses three-valued logic: comparisons with NULL yield NULL (unknown), and only TRUE rows pass a WHERE. UNIQUE treats NULLs as distinct by default, so multiple NULLs are allowed.

This is a Pro lesson

Get every Learning Pathway and cookbook recipe — grounded in PostgreSQL source code, with diagnostics, fixes, and prevention for each topic.

Continue this lesson to learn:

  • How to fix it
  • Prevent it next time
  • Related & next steps
  • All 36 Learning Pathway lessons
  • 170+ cookbook recipes
  • Source-grounded diagnostics & fixes
Unlock everything — $24.99/month $199/year — save 35%

Secure checkout Cancel anytime Source-grounded

Career Impact

This scenario builds production judgment and operational confidence under pressure.

Open Career Dashboard →

Keep going

Related & next steps

More like this

Connection slots nearly exhausted Finding tables with high dead tuple counts What the WAL is and why it makes you durable Synchronous Replication and Quorum Commit Synchronous standby not confirming — commits stalling

Concepts on this page

Constraint Index
Browse the cookbook Step-by-step recipes Follow a pathway Learn it in order Open the glossary Every concept, plainly
Was this helpful?

← All cookbook recipes