Postgres Calculations and the Ambiguity of NULL
13 min readMore by this author
When dividing by zero, Postgres fails and declares you ran an illegal operation. Cast 'abc' to an integer, and get an error. Divide by NULL and the query still runs. NULL isn't even a value. It is a marker for “unknown.” When using NULL, the concept of being unknown propagates through comparisons, arithmetic, concatenation, aggregates, window functions, and WHERE clauses. The result is well-defined, but it may not be the result you had in mind.
The unofficial subtitle of this post could be: why NOT NULL constraints are serious business. One way to dodge the complications below is to never store NULL in the first place. If a column should always have a value, say so in the schema.
Let's start with a quiz: what does this return?
SELECT (NULL = NULL) = (NULL != NULL);
If you said NULL, you are right. Both NULL = NULL and NULL != NULL are unknown, so the outer = is comparing unknown to unknown, which is also unknown. Comparison operators (=, <>, and the rest) return NULL when either side is unknown. That is why SQL has IS NULL instead of = NULL: you cannot know whether two unknowns are equal, but you can test whether a value is unknown. (There is a legacy caveat! It is at the bottom.)
Keep that in mind as we walk through the rest. NULL is not a value. It is unknown.
Three-Valued Logic with OR and AND
Boolean expressions in SQL are not limited to TRUE and FALSE. Every predicate can also be NULL, meaning unknown. WHERE and HAVING keep only rows where the expression is true. Unknown is discarded the same way false is.
SELECT
NULL = NULL AS null_eq_null, -- NULL
TRUE OR NULL AS true_or_null, -- t
FALSE OR NULL AS false_or_null, -- NULL
TRUE AND NULL AS true_and_null, -- NULL
FALSE AND NULL AS false_and_null, -- f
NOT NULL::boolean AS not_null, -- NULL
NULL::boolean IS UNKNOWN AS is_unknown; -- t
OR can still be true if the other side is true. AND can still be false if the other side is false. Anything else involving NULL collapses to unknown.
The following is a very basic query that drops a row you probably meant to keep:
CREATE TABLE flags (id int, active boolean);
INSERT INTO flags VALUES (1, true), (2, false), (3, NULL);
-- Rows 1 and 2 only. Row 3 is unknown, so it is filtered out.
SELECT * FROM flags WHERE active OR NOT active;
In two-valued logic active OR NOT active is a tautology (a statement that is true for every possible value, so it cannot fail). In SQL it is not. The usual fix is to decide what NULL should mean, then say so:
SELECT * FROM flags WHERE COALESCE(active, false);
SELECT * FROM flags WHERE active IS NOT TRUE; -- false and NULL
SELECT * FROM flags WHERE active IS UNKNOWN; -- NULL only
SELECT * FROM flags WHERE active IS DISTINCT FROM true;
IS TRUE, IS NOT TRUE, IS FALSE, IS NOT FALSE, and IS UNKNOWN are three-valued-logic escapes. They never return NULL. On a boolean, IS UNKNOWN is the same test as IS NULL.
The same idea for equality is IS NOT DISTINCT FROM. Ordinary = asks whether two values are known to be the same, so NULL = NULL is unknown. IS NOT DISTINCT FROM asks whether they are the same value, treating NULL as a value (I like to think of it as the state of being unknown). Two unknowns are not distinguishable, so the predicate is true:
SELECT
NULL = NULL AS eq, -- NULL
NULL IS NOT DISTINCT FROM NULL AS not_distinct, -- t
NULL IS DISTINCT FROM NULL AS is_distinct, -- f
1 IS NOT DISTINCT FROM NULL AS one_vs_null, -- f
1 IS DISTINCT FROM NULL AS one_is_distinct; -- t
Use it when a join, unique check, or WHERE should treat missing as missing, not as unknown. active = NULL never matches. active IS NOT DISTINCT FROM NULL does:
SELECT *
FROM flags
WHERE active IS NOT DISTINCT FROM NULL; -- NULL only, same as IS NULL here
A join ON a.x = b.x also drops rows where both sides are NULL. Switch that ON to IS NOT DISTINCT FROM when two missing keys should count as a match.
Any Arithmetic Operation Involving NULL Evaluates to NULL
Add NULL, multiply it, take its absolute value: the result is NULL.
SELECT
1 + NULL AS add_null, -- NULL
10 * NULL AS mul_null, -- NULL
NULL::integer / 2 AS div_null, -- NULL
abs(NULL::integer) AS abs_null, -- NULL
2 ^ NULL::integer AS pow_null; -- NULL
This shows up in reports as blank cells and in UPDATEs as wiped columns. You have probably written something like this:
UPDATE orders
SET total = quantity * unit_price; -- total becomes NULL if either input is
Use COALESCE at the point you know the business rule. Missing quantity is usually zero. Missing tax rate is usually zero. Missing unit price is usually not zero, and should stay NULL until someone fills it in.
SELECT quantity * COALESCE(unit_price, 0) AS line_total FROM orders;
There is one notable exception: aggregates, covered below.
If the goal is to keep NULL from reaching arithmetic in the first place, enforce NOT NULL constraints or check constraints on the columns.
The NOT IN Null Trap
IN and NOT IN are rewritten as chains of equality. Equality with NULL is unknown, and NOT IN is a chain of ANDs:
x NOT IN (1, 2, NULL)
≡ x <> 1 AND x <> 2 AND x <> NULL
≡ TRUE-or-FALSE AND TRUE-or-FALSE AND NULL
≡ NULL
Unknown is not true, so the row disappears. If the list or subquery contains even one NULL, NOT IN returns no rows.
CREATE TABLE products (id int, name text);
INSERT INTO products VALUES (1, 'widget'), (2, 'gadget'), (3, 'gizmo');
CREATE TABLE discontinued (product_id int);
INSERT INTO discontinued VALUES (2), (NULL);
-- Empty. Product 1 and 3 are not discontinued, but NOT IN cannot prove it.
SELECT name
FROM products
WHERE id NOT IN (SELECT product_id FROM discontinued);
IN is less catastrophic because it is a chain of ORs. A match is still true. A miss against a NULL is unknown, so you can still lose rows, but you do not lose every row the moment a NULL appears.
The reliable rewrite is NOT EXISTS, which uses WHERE equality and therefore never treats “compared to NULL” as a match:
SELECT p.name
FROM products p
WHERE NOT EXISTS (
SELECT 1
FROM discontinued d
WHERE d.product_id = p.id
);
An anti-join is the same idea, and often the plan you want anyway:
SELECT p.name
FROM products p
LEFT JOIN discontinued d ON d.product_id = p.id
WHERE d.product_id IS NULL;
Paul Ramsey’s Rise of the Anti-Join covers the performance side of this pattern, and How to Read Postgres EXPLAIN: A Guide to Scan Types is a good companion when you want to see how the planner is reading the tables. The ambiguity of NULL is why NOT IN is a poor default even before you look at the query plan.
If you must keep NOT IN, strip NULLs from the subquery:
SELECT name
FROM products
WHERE id NOT IN (
SELECT product_id FROM discontinued WHERE product_id IS NOT NULL
);
That only helps if you are sure a NULL in discontinued should be ignored. NOT EXISTS makes that meaning obvious.
Aggregates Ignore NULL
Here is a small survey. COUNT(*) counts rows. COUNT(column) counts non-null values. SUM, AVG, MIN, and MAX skip NULL inputs.
CREATE TABLE reviews (product_id int, rating int);
INSERT INTO reviews VALUES
(1, 5),
(1, 1),
(1, NULL), -- skipped the survey
(2, NULL),
(2, NULL);
SELECT
product_id,
COUNT(*) AS rows,
COUNT(rating) AS rated,
AVG(rating) AS avg_rating,
SUM(rating) AS sum_rating
FROM reviews
GROUP BY product_id
ORDER BY product_id;
product_id | rows | rated | avg_rating | sum_rating
------------+------+-------+--------------------+------------
1 | 3 | 2 | 3.0000000000000000 | 6
2 | 2 | 0 | |
If skipped ratings should count as zero, say so before the aggregate:
SELECT product_id, AVG(COALESCE(rating, 0)) AS avg_including_blanks
FROM reviews
GROUP BY product_id;
If they should not count, the default is already correct. What is not correct is mixing SUM(x) / COUNT(*) and expecting it to match AVG(x). (Also watch the types: SUM(rating) and COUNT(rating) are integers here, so / truncates unless you cast. 6 / 2 happens to be exactly 3. If the ratings were 5, 1, 2, then integer division would equal 2, and 2.66… from AVG.)
SELECT
AVG(rating) AS avg_skips_nulls,
SUM(rating) / COUNT(*) AS sum_over_all_rows,
SUM(rating)::numeric / COUNT(rating) AS same_as_avg
FROM reviews
WHERE product_id = 1;
AVG is SUM / COUNT(column), not SUM / COUNT(*). FILTER does not change that result: AVG(rating) already skips NULL. Write FILTER (WHERE rating IS NOT NULL) when you want the skip rule visible in the query. The form that does change the number is AVG(COALESCE(rating, 0)).
SELECT
AVG(rating) FILTER (WHERE rating IS NOT NULL) AS avg_rated, -- same as AVG(rating)
AVG(COALESCE(rating, 0)) AS avg_blanks_as_zero
FROM reviews;
Window Functions and NULL
When you use SUM or AVG as a window function, they still skip NULL inputs, the same as they do in GROUP BY. ROW_NUMBER() still counts the row. The mismatch is in lag, lead, first_value, last_value, and nth_value: those functions look at a specific position in the frame. If that position holds NULL, the result is NULL. They do not hunt for the nearest real value.
Here is a short temperature series with a dropped sample:
CREATE TABLE readings (ts int, temp numeric);
INSERT INTO readings VALUES
(1, 20),
(2, NULL), -- sensor dropped a sample
(3, 22);
SELECT
ts,
temp,
lag(temp) OVER (ORDER BY ts) AS prev_temp,
SUM(temp) OVER (ORDER BY ts) AS running_sum
FROM readings;
ts | temp | prev_temp | running_sum
----+------+-----------+-------------
1 | 20 | | 20
2 | | 20 | 20
3 | 22 | | 42
Filling that gap used to mean a subquery or a filtered DISTINCT ON. The plan is for Postgres 19 to add the SQL-standard null-treatment clause: RESPECT NULLS (the default, same as today) and IGNORE NULLS. Put it between the function arguments and OVER.
SELECT
ts,
temp,
lag(temp) OVER (ORDER BY ts) AS prev_respect,
lag(temp) IGNORE NULLS OVER (ORDER BY ts) AS prev_ignore
FROM readings;
ts | temp | prev_respect | prev_ignore
----+------+--------------+-------------
1 | 20 | |
2 | | 20 | 20
3 | 22 | | 20
IGNORE NULLS walks backward (or forward, for lead) until it finds a non-null argument, then applies the offset to those non-null rows only. first_value and last_value use the same clause. To see it, let's add the moment before the sensor came up, so the first row in the frame is NULL:
INSERT INTO readings VALUES (0, NULL);
SELECT
ts,
temp,
first_value(temp) OVER w AS first_respect,
first_value(temp) IGNORE NULLS OVER w AS first_ignore
FROM readings
WINDOW w AS (
ORDER BY ts
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);
ts | temp | first_respect | first_ignore
----+------+---------------+--------------
0 | | | 20
1 | 20 | | 20
2 | | | 20
3 | 22 | | 20
first_respect is NULL on every row because the first row in the frame is unknown. first_ignore is 20, the first real temperature.
This option is only valid on lag, lead, first_value, last_value, and nth_value. Ranking functions and window aggregates do not take it: they already have their own rules, and FILTER (WHERE … IS NOT NULL) is still the way to exclude nulls from a window SUM or AVG when you want that spelled out.
String Concatenation with NULL
The SQL || operator is arithmetic for strings: NULL in, NULL out. Put that next to concat, which treats missing pieces as empty.
SELECT 'Hello, ' || NULL || '!' AS greeting; -- NULL
SELECT NULL || 'suffix'; -- NULL
Build a display name from first_name and a nullable middle_name, and the whole name vanishes when middle is missing.
concat and concat_ws treat NULL as an empty string:
SELECT concat('Hello, ', NULL, '!'); -- Hello, !
SELECT concat_ws(' ', 'Ada', NULL, 'Lovelace'); -- Ada Lovelace
concat_ws also skips NULL arguments when placing the separator, so you do not get double spaces. That is the function you want for joining optional address lines or name parts.
COALESCE is the other option when empty string is the right substitute:
SELECT 'Hello, ' || COALESCE(middle_name, '') || '!';
Do not mix the two styles without looking. 'a' || NULL is NULL. concat('a', NULL) is 'a'. Application code that concatenates in SQL and then checks IS NULL to mean “all parts missing” will mis-fire if you switch to concat.
NULLs Sort Higher Than Real Values
Let's rank a tiny leaderboard. Postgres sorts NULL as larger than every non-null value. ORDER BY x ASC puts NULLs last. ORDER BY x DESC puts NULLs first.
CREATE TABLE scores (name text, points int);
INSERT INTO scores VALUES
('Ada', 10),
('Ben', NULL),
('Cam', 7);
SELECT * FROM scores ORDER BY points ASC;
-- Cam 7
-- Ada 10
-- Ben NULL
SELECT * FROM scores ORDER BY points DESC;
-- Ben NULL
-- Ada 10
-- Cam 7
If you wanted “highest score, missing scores at the bottom,” DESC alone puts Ben on top. Spell the null ordering:
SELECT * FROM scores ORDER BY points DESC NULLS LAST;
SELECT * FROM scores ORDER BY points ASC NULLS FIRST;
NULLS FIRST and NULLS LAST work with indexes too. If you always query ORDER BY points DESC NULLS LAST, match that in the index definition.
In a sort, NULL lands past the largest value, but MIN and MAX ignore it, so MAX(points) is 10. Sorting and aggregating don't share the NULL rule, which means they behave slightly differently.
A Short Checklist
If you've run into an issue where a calculation looks off:
WHEREdropped rows: the predicate is unknown. UseIS NULL/IS NOT NULL,IS UNKNOWN,IS NOT DISTINCT FROM,IS TRUE/IS NOT TRUE, orCOALESCE.- Empty result from
NOT IN: aNULLis in the list. Switch toNOT EXISTSor an anti-join. - Blank totals: arithmetic hit a
NULL.COALESCEat the business-rule boundary. - Average too high or too low:
AVGskippedNULLs. CompareCOUNT(*)withCOUNT(column). lag/first_valuereturned NULL: the neighbor in the frame is unknown. On Postgres 19, useIGNORE NULLS.- Missing names or labels:
||saw aNULL. Useconcat_ws. - Missing values sorted first on
DESC:NULLs sort high. AddNULLS LAST.
Postgres is following the SQL standard. 9.2. Comparison Functions and Operators states the comparison rule, and tells applications that expect expression = NULL to be true to change that:
Ordinary comparison operators yield null (signifying "unknown"), not true or false, when either input is null. For example,
7 = NULLyields null, as does7 <> NULL.Do not write
expression = NULLbecauseNULLis not "equal to"NULL. (The null value represents an unknown value, and it is not known whether two unknown values are equal.)
The Caveat: transform_null_equals
The quiz at the top returns NULL. transform_null_equals does not change this specific scenario, but it does change = NULL behavior. The setting makes = NULL look like IS NULL.
From 6.5 to 7.1, Postgres used to do this rewrite to help out Microsoft Access, whose filtered forms generate expr = NULL. As Thomas Lockhart put it on pgsql-general in null != null ???, it was “an explicit feature in our parser to help out poor MSAccess souls.” Postgres 7.2 defaulted the setting to off. When it is on, the parser rewrites the exact forms expr = NULL and NULL = expr into expr IS NULL. It does not rewrite !=, <>, IN, or two columns compared to each other.
SET transform_null_equals TO on;
SELECT
(NULL = NULL) = (NULL != NULL) AS quiz, -- still NULL
NULL = NULL AS null_eq_null, -- t
NULL = TRUE AS null_eq_true, -- f
NULL != NULL AS null_ne_null, -- NULL
(NULL = NULL) = NULL AS keyword_null; -- f
The first inner = becomes IS NULL (true). NULL = TRUE is the other exact form, so it becomes TRUE IS NULL (false). The != is left alone, so it stays unknown. The outer comparison is true = (NULL != NULL), and the right-hand side is an expression, not the keyword NULL, so the rewrite does not fire and the quiz stays unknown. Write the keyword on the right — (NULL = NULL) = NULL — and that becomes true IS NULL, which is false. You did not make two unknowns equal. You made Access happy, and only when it types = NULL or NULL = literally. Leave it off. Use IS NULL.
Disclaimer: of all the settings to change in Postgres, please don't change transform_null_equals.
Now that you know the ambiguity of unknown, decide what unknown means for each calculation, or whether it should exist in the schema at all.