NULL in PostgreSQL: Computation Pitfalls and Handling Strategies

NULL Is Not Zero, and Not an Empty String

NULL is one of the most misunderstood concepts in the SQL world. Many developers working with PostgreSQL instinctively treat NULL as "no value," overlooking its mathematical essence: NULL represents "unknown." This subtle distinction can introduce hard-to-debug bugs in aggregate calculations, conditional filtering, join queries, and more.

Understanding NULL's behavior is not only a prerequisite for writing correct SQL — it's also key to avoiding silent errors in database design. This article systematically covers the various ambiguous behaviors of NULL in PostgreSQL computations and offers practical handling strategies.

---

Three-Valued Logic: TRUE, FALSE, and UNKNOWN

Why Does NULL = NULL Return NULL?

SQL uses Three-Valued Logic (3VL), meaning any comparison involving NULL produces neither TRUE nor FALSE, but UNKNOWN. This means:

SELECT NULL = NULL;    -- Result: NULL (not TRUE)
SELECT NULL != NULL;   -- Result: NULL (not FALSE)
SELECT NULL = 1;       -- Result: NULL

This behavior directly affects WHERE clause filtering. When a condition evaluates to UNKNOWN, PostgreSQL excludes the corresponding row from the result set — the row neither satisfies nor fails to satisfy the condition; it exists in a kind of unresolved limbo.

The Fatal Trap of NOT IN Subqueries

NULL combined with three-valued logic most commonly causes silent full-result filtering in NOT IN subqueries:

-- If the subquery result contains any NULL, the entire query returns an empty set
SELECT * FROM orders
WHERE customer_id NOT IN (
    SELECT customer_id FROM blacklist  -- If this column has NULL, result is empty
);

The reason: x NOT IN (1, 2, NULL) is equivalent to x != 1 AND x != 2 AND x != NULL, and x != NULL is always UNKNOWN, causing the entire AND expression to collapse to UNKNOWN. Use NOT EXISTS instead:

SELECT * FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM blacklist b
    WHERE b.customer_id = o.customer_id
);

---

Aggregate Functions and NULL: Data That Gets Quietly Ignored

Two Behaviors of COUNT

PostgreSQL aggregate functions follow a unified rule: NULL values are ignored. However, this rule produces dramatically different results across different aggregate functions, and COUNT is the most illustrative example:

CREATE TABLE sample (val INT);
INSERT INTO sample VALUES (1), (2), (NULL), (NULL);

SELECT COUNT(*)   FROM sample;  -- 4 (counts all rows)
SELECT COUNT(val) FROM sample;  -- 2 (ignores NULL)
SELECT SUM(val)   FROM sample;  -- 3 (ignores NULL)
SELECT AVG(val)   FROM sample;  -- 1.5 (denominator is 2, not 4)

AVG's behavior deserves special attention: it computes the average of non-NULL values, not all rows. In analytical reports, if NULL means "0 purchases" rather than "missing data," you need AVG(COALESCE(val, 0)) to get semantically correct results.

Aggregate Results for All-NULL Columns

When an entire column is NULL, SUM, AVG, MAX, and MIN all return NULL — not 0, and not an error. This can trigger division by zero or unexpected NULL propagation when computing ratios:

-- Potential issue: when denominator is NULL, the entire expression is NULL
SELECT SUM(revenue) / SUM(quantity) AS avg_price FROM sales;

-- Safer approach
SELECT
    CASE WHEN SUM(quantity) > 0
         THEN SUM(revenue) / SUM(quantity)
         ELSE NULL
    END AS avg_price
FROM sales;

---

NULL Position in Sorting and Window Functions

Default Sort Order of NULL in ORDER BY

PostgreSQL treats NULL as the largest value by default — placing it last in ascending (ASC) order and first in descending (DESC) order. This is the opposite of MySQL's behavior (where NULL is treated as the smallest value), making it a common pitfall during cross-database migrations:

SELECT val FROM sample ORDER BY val ASC;
-- Result: 1, 2, NULL, NULL

SELECT val FROM sample ORDER BY val DESC;
-- Result: NULL, NULL, 2, 1

You can explicitly control NULL's sort position using NULLS FIRST or NULLS LAST:

SELECT val FROM sample ORDER BY val ASC NULLS FIRST;
-- Result: NULL, NULL, 1, 2

NULL Partitioning in Window Functions

In window functions, NULL values in PARTITION BY deserve attention: all rows with a NULL partition key are grouped into the same partition. This is sometimes intentional and sometimes not. When designing analytical queries, always clarify the business meaning of a NULL partition.

---

COALESCE, NULLIF, and IS DISTINCT FROM

Three Essential Tools for Handling NULL

PostgreSQL provides several functions and operators specifically designed for NULL. Mastering them significantly improves the robustness of your SQL code:

COALESCE: Returns the first non-NULL value in the argument list — commonly used to set default values:

SELECT COALESCE(discount, 0) AS effective_discount FROM products;

NULLIF: Returns NULL when two arguments are equal — commonly used to prevent division-by-zero errors:

SELECT revenue / NULLIF(quantity, 0) AS unit_price FROM sales;

IS DISTINCT FROM: A NULL-safe comparison operator that treats NULL as a concrete, comparable value:

SELECT * FROM t WHERE a IS DISTINCT FROM b;
-- Equivalent to: a != b, but also correctly handles NULL cases
-- NULL IS DISTINCT FROM NULL → FALSE
-- NULL IS DISTINCT FROM 1  → TRUE

This operator is especially useful for change detection (CDC) or audit logs, correctly identifying changes such as "from a value to NULL" or "from NULL to a value."

---

NULL Behavior in Indexes and Constraints

Unique Indexes Do Not Prevent Multiple NULLs

PostgreSQL's unique indexes follow the SQL standard: NULL does not equal NULL, so a unique column can contain multiple NULL values. If your business logic requires "at most one NULL," you need a partial index or a newer feature:

-- Allows multiple NULLs, but non-NULL values must be unique
CREATE UNIQUE INDEX ON users(email);
-- Multiple rows with email = NULL can be inserted

-- PostgreSQL 15+ supports the NULLS NOT DISTINCT option
CREATE UNIQUE INDEX ON users(email) NULLS NOT DISTINCT;

NULLS NOT DISTINCT (introduced in PostgreSQL 15) is an important improvement — it allows unique constraints to treat NULL as equal to NULL, filling a long-standing semantic gap.

CHECK Constraints and Their Permissive Behavior with NULL

CHECK constraints pass validation when the condition evaluates to UNKNOWN (i.e., involves NULL), rather than rejecting the insert. This is often counterintuitive:

ALTER TABLE products ADD CONSTRAINT price_positive CHECK (price > 0);
-- Inserting price = NULL will succeed, because NULL > 0 is UNKNOWN, not FALSE

If you need to enforce both non-null and condition compliance, you must add a NOT NULL constraint as well.

---

Best Practices Summary

NULL's ambiguity is rooted in SQL's three-valued logic design — it cannot be avoided, but it can be tamed through disciplined coding habits:

• In JOIN conditions and WHERE filters, use IS NULL / IS NOT NULL instead of = NULL / != NULL
• Avoid NOT IN with subqueries that may contain NULL — use NOT EXISTS or LEFT JOIN + IS NULL instead
• Clarify the business meaning of NULL before aggregate calculations — use COALESCE to distinguish between missing data and zero
• When comparing whether two columns differ, prefer IS DISTINCT FROM over !=
• For PostgreSQL 15+ projects, consider NULLS NOT DISTINCT for unique columns that allow NULL
• During cross-database migrations, explicitly specify NULLS FIRST / NULLS LAST rather than relying on default sort behavior

At its core, NULL is a model for "the unknown." When designing table schemas, every nullable column deserves the question: does NULL here mean "we don't know," "this value doesn't apply," or "the user didn't fill this in"? Encoding these three semantics differently — with separate boolean columns, enum values, or default values — is far more reliable than patching NULL repeatedly at the query layer.