Postgres NULL Handling: Three-Valued Logic and the Bugs It Hides

SQL inherits two-valued Boolean logic from mathematics — every predicate is either true or false — and then breaks it by adding NULL as a third possible result. The combination is what most relational databases call three-valued logic. Postgres implements it faithfully and consistently. The faithfulness is the problem. Code that looks like it should work by reading it does not work, and the failure mode is silently returning wrong results rather than raising an error.

Most teams encounter the bugs piecemeal — a missing row in a report, a duplicate row after a deduplication query, a customer that should be in a segment but is not. The fixes are mechanical once the cause is identified, but the cause is not obvious because the bug is in the operator semantics rather than the code structure.

What NULL actually means

NULL is the SQL standard's way to mark a column value as unknown or inapplicable. The semantics chosen for it derive from the requirement that NULL propagate through expressions without polluting them with arbitrary defaults. Adding 5 to NULL produces NULL, not 5 and not 0. Comparing NULL to anything else produces NULL, not true and not false. Negating NULL produces NULL, not true. The propagation is consistent and the rules are simple to state.

The catch is that WHERE clauses and JOIN conditions and CHECK constraints require Boolean results, not three-valued results. The SQL standard resolves the conflict by treating NULL as "not true" — so a WHERE clause that evaluates to NULL filters the row out. The rule is consistent and the result is silently dropping rows where the predicate could not be evaluated as definitely true.

The four-pattern set that catches most teams

The first pattern is the equality trap. WHERE column = NULL is always NULL and filters out every row. The fix is WHERE column IS NULL which returns true or false but never NULL. The two look syntactically similar enough that the bug survives review. Postgres will not warn about it because the syntax is legal SQL.

-- Wrong: returns zero rows
SELECT * FROM users WHERE deleted_at = NULL;

-- Right
SELECT * FROM users WHERE deleted_at IS NULL;

The second pattern is the NOT IN trap. WHERE id NOT IN (SELECT user_id FROM blocked) returns zero rows if any row in the blocked subquery has NULL user_id, because id != NULL is NULL which fails the predicate. The fix is NOT EXISTS or a left join with NULL check. Like the equality trap, this one survives review because the query reads like it should work.

-- Wrong: returns zero rows if any blocked.user_id is NULL
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blocked);

-- Right
SELECT * FROM users WHERE NOT EXISTS (
  SELECT 1 FROM blocked WHERE blocked.user_id = users.id
);

The third pattern is the count and aggregate divergence. COUNT(*) counts all rows including those with NULL columns. COUNT(column) counts only non-NULL values of that column. SUM and AVG and MIN and MAX all skip NULL silently. The result is queries that look like they count the same thing in two ways producing different numbers, with the discrepancy being the NULL count.

The fourth pattern is the unique constraint surprise. Postgres unique constraints allow multiple NULL values by default because NULL is not equal to NULL. UNIQUE (email) on a users table will accept any number of rows with NULL email. The fix is either NOT NULL on the column or the Postgres 15 addition UNIQUE NULLS NOT DISTINCT that treats NULLs as equal for uniqueness purposes.

The three operators that handle NULL correctly

The operators that work the way most developers expect with NULL are IS NULL and IS NOT NULL for explicit NULL checks, IS DISTINCT FROM and IS NOT DISTINCT FROM for equality comparison that treats NULL as a value, and COALESCE for selecting the first non-NULL value from a list.

The IS DISTINCT FROM operator is underused given how often the desired semantics are "are these two values different, treating NULL as a value." It returns true if exactly one operand is NULL, false if both are NULL, and the standard comparison otherwise. The fix for many subtle bugs is replacing != with IS DISTINCT FROM.

-- Wrong: misses rows where old or new is NULL
WHERE old_status != new_status;

-- Right
WHERE old_status IS DISTINCT FROM new_status;

COALESCE is the right pattern for "use this value unless it's NULL, in which case use a default." It evaluates left-to-right and returns the first non-NULL argument. The common mistake is using it where the right answer is IS NULL — COALESCE returns a value but does not change the underlying semantics of the comparison.

What CHECK constraints accept

CHECK constraints in Postgres reject rows where the expression evaluates to false. They accept rows where the expression evaluates to true or NULL. This is the same rule as WHERE clauses but applied to the insert side, and it produces a different class of bug.

-- This constraint accepts NULL amount
CHECK (amount > 0);

-- To require positive amount, combine with NOT NULL
amount NUMERIC NOT NULL CHECK (amount > 0);
-- or check explicitly
CHECK (amount IS NOT NULL AND amount > 0);

The pattern of writing CHECK constraints without explicit NULL handling is widespread, and the bugs it creates are subtle. A row with NULL in a checked column passes validation. The schema appears to enforce a constraint that it does not actually enforce. Production data accumulates rows that violate the apparent invariant.

What this tells us about Postgres design

Postgres implements three-valued logic faithfully because the SQL standard requires it. The implementation is consistent and the rules are documented. The bugs that result are not Postgres bugs — they are gaps between developer mental models of SQL semantics and the actual semantics the standard specifies.

The defenses that compound are explicit NOT NULL constraints on columns where NULL is meaningless, IS DISTINCT FROM for equality comparisons where either side might be NULL, NOT EXISTS in place of NOT IN for set membership negation, and explicit IS NULL or IS NOT NULL in CHECK constraints. Each individual defense is mechanical. The discipline is applying them consistently before the bugs accumulate.

Our use across DocuMint and CronPing and FlagBit and WebhookVault converges on NOT NULL by default with explicit nullable columns documented in schema comments. The fastest way to catch NULL bugs in code review is to require that any nullable column have an explicit comment justifying the nullability. The discipline turns the absence of NOT NULL from an oversight into an intentional design decision.

The deeper observation is that SQL's three-valued logic is one of the cleanest cases of a design choice that is consistent and correct by its own rules and produces a steady stream of bugs anyway because developer intuitions about logic do not match the rules. The fix is not changing SQL — too much code depends on the current semantics — but adopting the operators and constraints that close the gap between intuition and standard behavior.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.