NULL in Postgres: Why Your Calculations Silently Return the Wrong Result

NULL is not a value. It's a marker for "unknown," and that distinction, seemingly philosophical, is the source of an entire class of bugs that pass every test and only show up in production, when someone asks why the report's total is showing zero. Christopher Winslett's article on the Crunchy Data blog, Postgres Calculations and the Ambiguity of NULL, precisely walks through the points where this ambiguity contaminates comparisons, arithmetic, aggregations, window functions, concatenation, and ordering.

The starting point is an asymmetry every DBA has already noticed without necessarily stopping to name it: dividing by zero raises an error (division by zero), converting 'abc' to an integer raises an error, but dividing by NULL runs normally and returns NULL. The database isn't failing: it's applying the SQL standard's three-valued logic, in which true, false, and unknown coexist. The result is well defined. It just isn't what the developer had in mind.

Three-valued logic and the WHERE that drops rows

The rule Winslett sets out as fundamental: comparison operators (=, <>, <, >) return NULL whenever one side is unknown. That's why NULL = NULL isn't true, it's NULL, and that's why SQL has IS NULL instead of = NULL. There's no way to know whether two unknowns are equal.

The practical danger lives in WHERE and HAVING: they keep only rows whose expression is true. Both false and unknown are discarded. The article's example is instructive precisely because it looks harmless:

CREATE TABLE flags (id int, active boolean);
INSERT INTO flags VALUES (1, true), (2, false), (3, NULL);

-- Returns only rows 1 and 2. Row 3 (NULL) is filtered out.
SELECT * FROM flags WHERE active OR NOT active;

In classical logic, active OR NOT active is a tautology: always true. In SQL it isn't, because NULL OR NOT NULL collapses into unknown, and unknown disappears. The explicit escape hatches are COALESCE(active, false), active IS NOT TRUE, active IS UNKNOWN, or active IS DISTINCT FROM true. The forms IS TRUE, IS NOT TRUE, IS FALSE, IS UNKNOWN have the virtue of never returning NULL, always true or false.

For equality, the equivalent is IS NOT DISTINCT FROM, which treats NULL as if it were a value: two unknowns are indistinguishable, so NULL IS NOT DISTINCT FROM NULL is true. A detail that often goes unnoticed in migrations: a JOIN ON a.x = b.x drops rows where both sides are NULL. If the intent is for two missing keys to count as a match, the correct clause is ON a.x IS NOT DISTINCT FROM b.x.

The NOT IN trap, the most destructive on the list

Of all the pitfalls, this is the one that wipes out an entire result set with no warning. IN and NOT IN are rewritten as chains of equality. Since NOT IN becomes a chain of AND, a single NULL in the list is enough for the predicate to collapse into unknown:

x NOT IN (1, 2, NULL)
 \u2261 x <> 1 AND x <> 2 AND x <> NULL
 \u2261 (true/false) AND (true/false) AND NULL
 \u2261 NULL

The practical effect is brutal: if the NOT IN subquery returns even a single NULL, the result is empty. In the article's example, a discontinued table with values (2), (NULL) makes SELECT ... WHERE id NOT IN (SELECT product_id FROM discontinued) return no products at all, even ones that clearly aren't discontinued. The database can't prove they're not in the list because it doesn't know what that NULL represents.

The reliable rewrite is NOT EXISTS, which operates with equality inside WHERE and never treats a comparison with NULL as a match:

SELECT p.name
FROM products p
WHERE NOT EXISTS (
 SELECT 1 FROM discontinued d WHERE d.product_id = p.id
);

The anti-join with LEFT JOIN ... WHERE d.product_id IS NULL produces the same meaning and, incidentally, tends to be the execution plan the planner prefers. Winslett points to Paul Ramsey's piece "Rise of the Anti-Join" for the performance angle. A DBA's takeaway here: NOT IN is already a bad pattern on semantic grounds before you even open EXPLAIN. If you can't avoid it, filter out the NULLs in the subquery with WHERE product_id IS NOT NULL, but that's only correct if ignoring the NULL is really the intended business rule, and NOT EXISTS makes that intent explicit.

Aggregations ignore NULL, and that's where the average lies

The rule is simple to state and easy to forget: COUNT(*) counts rows; COUNT(column) counts only non-null values; SUM, AVG, MIN, and MAX skip NULL. The problem shows up when the developer mixes up the counts:

| Expression | What it does | |---|---| | AVG(rating) | sum of non-null values / COUNT(rating) | | SUM(rating) / COUNT(*) | sum of non-null values / total row count (lower than the real average) | | AVG(COALESCE(rating, 0)) | treats absence as zero (changes the number) |

AVG(x) is SUM(x) / COUNT(column), never SUM(x) / COUNT(*). And there's a type trap: SUM(rating) and COUNT(rating) are integers, so / truncates unless you cast to numeric. Winslett also clears up a common misunderstanding about FILTER: writing AVG(rating) FILTER (WHERE rating IS NOT NULL) doesn't change the result, because AVG already skips NULL. FILTER is there to make the rule visible in the query, not to change it. Anyone who wants to change the number uses COALESCE.

Window functions and what's new in Postgres 19

In windows, SUM and AVG still skip NULL just as in GROUP BY, and ROW_NUMBER() still counts the row. The mismatch is in lag, lead, first_value, last_value, and nth_value: these functions look at a specific position within the frame. If that position holds NULL, the return is NULL, with no hunting for the nearest real value. In a temperature series where a sensor missed one reading, lag(temp) on the row right after the gap returns the gap's NULL, not the previous 20 degrees.

Until now, the solution required a subquery or a filtered DISTINCT ON. The article notes that Postgres 19 plans to add the SQL-standard null-handling clause: RESPECT NULLS (the current behavior) and IGNORE NULLS, positioned between the function's arguments and OVER:

SELECT ts, temp,
 lag(temp) IGNORE NULLS OVER (ORDER BY ts) AS prev_ignore
FROM readings;

With IGNORE NULLS, the function walks backward (or forward, for lead) until it finds a non-null argument and applies the offset only across the real rows. It only applies to the five positional functions mentioned: ranking functions and window aggregates still use FILTER (WHERE ... IS NOT NULL).

Concatenation and ordering: two details that corrupt output

The || operator is arithmetic for strings: NULL in, NULL out. 'Hello, ' || NULL || '!' results in NULL, and a display name built with first_name || ' ' || middle_name disappears entirely when the middle name is null. concat and concat_ws, on the other hand, treat NULL as an empty string, and concat_ws also skips NULLs when placing the separator, avoiding doubled spaces. Winslett's warning is operational: don't swap one for the other without reviewing the impact. Application code that concatenates in SQL and then tests IS NULL to mean "all parts are missing" will break if migrated from || to concat.

In ordering, Postgres treats NULL as greater than any non-null value. ORDER BY x ASC pushes NULLs to the end; ORDER BY x DESC brings them to the top. An ORDER BY points DESC on a scoreboard puts whoever has no score in first place. The fix is explicit: NULLS LAST or NULLS FIRST, which also work in index definitions, a point the DBA needs to match between query and index to avoid losing index use for the sort. And it's worth noting the internal inconsistency: in ORDER BY, NULL sits above the highest value, but MAX(points) ignores it and returns the real highest. Ordering and aggregation don't share the same NULL rule.

What this means for those building systems

The underlying lesson, which Winslett leaves as an unofficial subtitle, is that NOT NULL constraints are serious business. The cheapest way to never deal with these seven pitfalls is to never store NULL where the column should always have a value. That's a schema design decision: defining a column as required, or using CHECK constraints, keeps the unknown from ever reaching the arithmetic. When NULL is legitimate (a unit price not yet filled in, for example), the rule is to decide its meaning at the exact point where the business rule is known, usually with COALESCE at the boundary of the calculation.

There's also a warning worth highlighting for anyone inheriting old databases: the transform_null_equals parameter, created between versions 6.5 and 7.1 to accommodate Microsoft Access forms that generated expr = NULL, rewrites = NULL as IS NULL when turned on. It's been off by default since Postgres 7.2, and Winslett is emphatic:

Of all the settings to change in Postgres, please don't change transform_null_equals.

-- Christopher Winslett, Crunchy Data

For the Brazilian developer migrating or scaling a database in production, the article's closing checklist works as a diagnostic: rows disappearing in WHERE, empty results from NOT IN, blank totals, an average that's off, lag returning NULL, missing names, or null values at the top of a DESC sort. All of them point to the same place. Before scaling up hardware or rewriting the execution plan, it's worth confirming whether the calculation simply ran into an unknown that nobody decided the meaning of.