beginnerintermediateadvanced

PostgreSQL · Section 7

NULL, Three-Valued Logic and Data Semantics

Level: intermediate
Read: 34 min
Concepts: 6

What NULL actually claims about data (nothing recorded, not zero), the operators built specifically to test for it, one underlying rule that explains NULL's different-looking effects across WHERE, JOIN, aggregates and GROUP BY, COALESCE and NULLIF as the two functions that convert between NULL and a real value, the NOT IN pitfall that is one of SQL's best-known traps, and the schema-design discipline of making every NULL-able column mean something specific.

PostgreSQL overview

Learn PostgreSQL Tutorial - Full Course for BeginnersfreeCodeCamp.org

What is true here

NULL means no value was recorded — not 0, not '', not false.
IS NULL/IS NOT NULL always return true or false; = NULL always returns NULL.
One rule — any comparison with NULL is unknown — explains its effect across WHERE, JOIN ON, and boolean expressions.
GROUP BY deliberately groups all NULLs together — the one exception to "NULL is never equal to NULL."
NOT IN against any list containing a NULL — literal or subquery — always returns zero rows, silently.

What you will be able to do

Explain why NULL is not the same as zero, empty string or false, and why that matters for aggregates
Use IS NULL/IS NOT NULL and IS NOT DISTINCT FROM instead of = for NULL-aware comparisons
Predict NULL's effect across WHERE, JOIN, aggregates and GROUP BY from one underlying rule
Recognize and avoid the NOT IN + NULL pitfall in both literal lists and subqueries
Decide deliberately whether a column should be NULL-able, with an articulable reason either way

From what NULL means to designing for it deliberately

NULL = no value recorded

not 0, not '', not false

One rule, many clauses

WHERE, JOIN, aggregates, GROUP BY

The NOT IN trap

the most consequential real-world failure mode

Design NULL-ability on purpose

every NULL-able column needs a "why"

What NULL means

Absence, not zero — and the operators built specifically to test for it.

NULL as Unknown/Missing, Not an Ordinary Value

corebeginner

NULL is not zero, not an empty string, and not false — it is the absence of a known value. A numeric column that is NULL is not "0," a text column that is NULL is not "''," and a boolean column that is NULL is not "false." Each of those is a real, specific value; NULL is the claim that no value is recorded at all.

Think of it as

Every other value in a column is a specific fact: 0 means "the count is exactly zero," '' means "the string is exactly empty." NULL makes no factual claim about the data at all — it says the fact is not present in this row, for whatever reason the schema allows (not yet known, not applicable, deliberately withheld). Treating NULL as "the smallest/default value of its type" is exactly the mistake this distinction guards against.

sql

SELECT count(*) AS all_rows, count(bonus) AS rows_with_a_bonus, avg(bonus) AS avg_of_known_bonuses
FROM employees;

What we're doing: Show that AVG and COUNT(col) skip NULL rows entirely, rather than treating NULL as zero.

null_vs_zero.sqlsql

CREATE TABLE employees (name TEXT, bonus NUMERIC);
INSERT INTO employees (name, bonus) VALUES ('Ada', 1000), ('Grace', 0), ('Linus', NULL);

SELECT count(*) AS all_employees,
       count(bonus) AS employees_with_a_recorded_bonus,
       avg(bonus) AS avg_bonus
FROM employees;

2: Grace's bonus is genuinely 0 (a fact); Linus's bonus is NULL (no fact recorded).
4–6: count(*) counts all 3 rows; count(bonus) counts only the 2 rows with a real value, skipping Linus.

Output

all_employees | employees_with_a_recorded_bonus | avg_bonus
---------------+----------------------------------+-----------
             3 |                                2 |    500.0000000000000000

Why this works: avg_bonus is 500 (the average of 1000 and 0), not 333.33 (the average of 1000, 0 and 0-for-Linus) — PostgreSQL never substitutes 0 for a NULL bonus, it simply excludes Linus's row from the computation entirely, dividing by 2 known values, not 3. This is the concrete consequence of NULL meaning "no value recorded" rather than "the value is zero": treating them the same would silently understate the true average whenever some rows genuinely have no data.

Assuming AVG divides by every row, including NULLs, as if they were zero

Wrong

sql

-- expecting avg_bonus to reflect all 3 employees, treating Linus's NULL as 0
SELECT avg(bonus) FROM employees;
-- actually divides only by the 2 rows with a real bonus value

Better

sql

SELECT avg(COALESCE(bonus, 0)) FROM employees;
-- explicitly treats a missing bonus as 0, if that is truly the intended semantics

What you see: A computed average, sum, or other aggregate looks too high compared to what a reader expected if they assumed every row (including NULL ones) contributed to the divisor.

Why: PostgreSQL's aggregate functions are specified to ignore NULL input values entirely — this is the correct behavior when NULL genuinely means "this employee's bonus is not applicable or not yet known," since including an unknown value as though it were zero would be asserting a fact nobody actually recorded. When the intended semantics really is "treat missing as zero," COALESCE(bonus, 0) makes that substitution explicit and visible in the query, rather than relying on an aggregate function's default behavior to happen to match the intent.

A real "zero-like" value vs a genuinely missing one

bonus = 0

+a fact: this employee's bonus is exactly zero
+participates normally in SUM/AVG
+counted by COUNT(bonus)

bonus = NULL

−no fact recorded — bonus amount unknown/not applicable
−skipped entirely by SUM/AVG, not treated as 0
−NOT counted by COUNT(bonus)

NULL vs the nearest "empty-looking" real value, per type

NULL vs the nearest "empty-looking" real value, per type
Type	A real value that looks "empty"	NULL
integer	0 — a specific count	no count recorded at all
text	'' — a specific zero-length string	no string recorded at all
boolean	false — a specific negative answer	no answer recorded at all

Together

sql

SELECT avg(bonus) FROM employees;
-- NULL bonuses are skipped entirely, NOT averaged in as 0

Remember: NULL means no value was recorded — not zero, not empty string, not false. Aggregate functions like SUM/AVG/COUNT(col) skip NULL rows entirely rather than treating them as zero.

IS NULL and IS NOT NULL

standardbeginner

IS NULL and IS NOT NULL are the only operators built specifically to test for the presence or absence of a value — they always return true or false, never NULL, which is what makes them safe to use in a WHERE clause where = NULL would silently fail.

Think of it as

IS is not a comparison operator like = — it is a predicate specifically about a value's "nullness," a property every value has (either it is NULL or it is not), so the question always has a definite yes/no answer. This is exactly why SQL needed to invent separate syntax for it: three-valued logic makes = fundamentally unable to answer this question, but IS NULL was designed from the start to sidestep the whole problem.

sql

SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE phone IS NOT NULL;
SELECT a IS NOT DISTINCT FROM b;  -- true even when both a and b are NULL

What we're doing: Compare IS NULL against IS NOT DISTINCT FROM to see how each treats a genuine NULL-vs-NULL question.

is_null_demo.sqlsql

SELECT NULL = NULL AS eq_result,
       NULL IS NULL AS is_null_result,
       NULL IS NOT DISTINCT FROM NULL AS distinct_result;

1: = always returns NULL when either side is NULL, including NULL = NULL.
2: IS NULL is a presence test, not a comparison — it correctly returns true.
3: IS NOT DISTINCT FROM is defined to treat two NULLs as equal, giving true where = gives NULL.

Output

eq_result | is_null_result | distinct_result
----------+----------------+-----------------
          | t              | t

Why this works: eq_result prints blank because NULL = NULL genuinely is NULL, not false — psql renders NULL as an empty cell, which itself is a common source of confusion since it looks like nothing happened rather than "the answer is unknown." IS NULL and IS NOT DISTINCT FROM were both specifically designed to never produce that ambiguous blank result — they commit to a real true or false answer for exactly the cases where = refuses to.

Reaching for = when the actual need is "are these two nullable values the same"

Wrong

sql

SELECT * FROM a JOIN b ON a.optional_ref = b.optional_ref;
-- rows where BOTH sides are NULL never match, even though "both missing" might mean "the same"

Better

sql

SELECT * FROM a JOIN b ON a.optional_ref IS NOT DISTINCT FROM b.optional_ref;
-- rows where both sides are NULL now match too

What you see: A join or comparison intended to treat "both sides missing" as a match silently excludes those rows, because = between two NULLs never produces true.

Why: = inherits three-valued logic regardless of which two things are being compared, so two NULL values are never asserted equal by = — this is correct when NULL genuinely means "unrelated unknowns," but wrong when the actual business meaning is "both records agree that this field is not applicable." IS NOT DISTINCT FROM exists precisely for that second case, defining NULL as equal to NULL by explicit design rather than by the general rules of comparison.

IS NULL vs IS DISTINCT FROM — two ways to sidestep three-valued logic

IS NULL vs IS DISTINCT FROM — two ways to sidestep three-valued logic
Expression	Always true/false?	What it tests
col = NULL	no — always NULL	invalid — never use this
col IS NULL	yes	is this value absent?
a IS NOT DISTINCT FROM b	yes	are these equal, treating two NULLs as equal?

Together

sql

SELECT NULL IS NOT DISTINCT FROM NULL;  -- true -- unlike NULL = NULL, which is NULL

Remember: IS NULL/IS NOT NULL always return true or false, never NULL — they test presence, not value, which is why they replace = for this specific question. IS NOT DISTINCT FROM extends the same idea to full comparisons.

See also: null as unknown · null semantics

Where NULL shows up

One rule across comparisons, joins, aggregates and boolean expressions, plus the two functions that convert to and from it.

How NULL Affects Comparisons, Joins, Aggregates and Boolean Expressions

coreintermediate

One rule explains all of NULL's behavior across SQL: any comparison involving NULL evaluates to NULL (unknown), and NULL is treated as not-true everywhere a true/false decision is required. This single rule produces different-looking effects in WHERE, JOIN ON, aggregates and boolean expressions — but it is the same rule every time.

Think of it as

Rather than memorizing "NULL does X in WHERE, Y in JOIN, Z in aggregates" as separate facts, it is one fact applied in four places: a JOIN predicate is just a WHERE-like boolean test, so a NULL join key never matches (same reason NULL never equals NULL); an aggregate function is defined to skip NULL inputs rather than propagate them; and a boolean expression combining conditions with AND/OR follows the same three-valued truth tables regardless of which clause it sits in.

sql

SELECT region, count(*) FROM customers GROUP BY region ORDER BY region NULLS LAST;

What we're doing: Show GROUP BY grouping all NULLs together — the one place NULL behaves as if it equals another NULL — contrasted with a plain WHERE comparison.

null_across_clauses.sqlsql

CREATE TABLE customers (id SERIAL PRIMARY KEY, region TEXT);
INSERT INTO customers (region) VALUES ('west'), (NULL), (NULL), ('east');

SELECT region, count(*) FROM customers GROUP BY region ORDER BY region NULLS LAST;

-- contrast: WHERE never groups NULLs, it excludes them from a direct comparison
SELECT count(*) FROM customers WHERE region = (SELECT region FROM customers WHERE id = 2);
-- customer 2's region is NULL -- this always returns 0, for the same = NULL reason

2: Two customers have NULL region — GROUP BY will treat them as one combined group.
4: GROUP BY produces exactly 3 groups: west, east, and one NULL group holding both NULL rows.
7–8: A direct = comparison against a NULL subquery result behaves like any other NULL comparison — always excluded, never grouped.

Output

region | count
-------+------
east   |     1
west   |     1
       |     2
(3 rows)

 count
-------
     0
(1 row)

Why this works: GROUP BY is specifically defined to treat all NULL values in the grouping column as members of one group — a deliberate, documented exception to the usual "NULL is never equal to NULL" rule, made because grouping needs some way to bucket rows with a missing value together rather than creating a separate group per row. The WHERE comparison in the second query does not get this exception: region = <a NULL value> follows the ordinary three-valued rule and simply never matches, returning 0 regardless of how many rows actually have a NULL region.

Assuming GROUP BY's NULL-grouping exception applies to WHERE or JOIN as well

Wrong

sql

SELECT a.*, b.* FROM a JOIN b ON a.region = b.region;
-- assuming rows where BOTH a.region and b.region are NULL will match, the way GROUP BY would group them

Better

sql

SELECT a.*, b.* FROM a JOIN b ON a.region IS NOT DISTINCT FROM b.region;
-- explicit: NULL region on both sides now counts as a match

What you see: A join expected to match "both sides have no region set" against each other silently drops those pairs, even though the same NULL values would have landed in the same GROUP BY group elsewhere in the codebase.

Why: GROUP BY's NULL-grouping behavior is a special case scoped only to grouping — it does not change how = behaves anywhere else, including inside a JOIN's ON clause, which is still an ordinary three-valued comparison. Carrying an assumption from one clause to another is the actual mistake here; IS NOT DISTINCT FROM is the explicit way to get NULL-equals-NULL behavior in a JOIN, since ON does not inherit GROUP BY's exception automatically.

One rule, four clauses

WHERE / JOIN ON

NULL comparison never true

Aggregates

NULL inputs skipped

GROUP BY

NULLs grouped together — the exception

ORDER BY

NULLS FIRST/LAST controls placement

The same NULL rule, four different clauses

The same NULL rule, four different clauses
Clause	What happens with a NULL involved
WHERE / JOIN ON	a NULL comparison is never true — row/match excluded
Aggregate functions	NULL input rows are skipped, not treated as zero
GROUP BY	all NULLs are grouped together as one group — an exception to "NULL ≠ NULL"
ORDER BY	NULLs sort first or last, controlled by NULLS FIRST/LAST

Together

sql

SELECT region, count(*) FROM customers GROUP BY region;
-- every customer with region = NULL lands in one "NULL" group, not scattered

Remember: One rule — any comparison with NULL is unknown, and unknown is treated as not-true — explains WHERE, JOIN ON, and boolean expressions. GROUP BY is the one deliberate exception, grouping all NULLs together.

COALESCE and NULLIF

standardbeginner

COALESCE(a, b, c, ...) returns the first non-NULL argument — the standard way to supply a default for a missing value. NULLIF(a, b) returns NULL if a equals b, otherwise returns a — the standard way to turn a sentinel value into a real NULL.

Think of it as

COALESCE and NULLIF are exact opposites, both solving the same underlying problem from different directions: real-world data often needs to move between "missing" and "a specific placeholder value" at some boundary. COALESCE converts NULL into something else, usually a display default. NULLIF converts something else (a sentinel like -1, or an empty string) into a genuine NULL, so the rest of the query's NULL-handling logic applies to it correctly.

sql

SELECT COALESCE(phone, 'no phone on file') FROM customers;
SELECT NULLIF(status, 'unset') FROM tasks;
SELECT COALESCE(NULLIF(status, 'unset'), 'pending') FROM tasks;

What we're doing: Use NULLIF to convert a legacy sentinel value into a real NULL, then COALESCE to supply a display default — chained together in one expression.

coalesce_nullif.sqlsql

CREATE TABLE tasks (id SERIAL PRIMARY KEY, status TEXT);
INSERT INTO tasks (status) VALUES ('done'), ('unset'), (NULL);
-- 'unset' is a legacy sentinel from an older system version, meaning the same as NULL

SELECT id, status,
       NULLIF(status, 'unset') AS normalized,
       COALESCE(NULLIF(status, 'unset'), 'pending') AS display_status
FROM tasks;

2: Three rows: a real status, a legacy sentinel meaning "no status," and an actual NULL.
5: NULLIF(status, 'unset') converts the sentinel row's value to a genuine NULL, leaving the other two rows unchanged.
6: COALESCE then supplies 'pending' for BOTH the normalized sentinel and the original NULL, since both are now the same NULL value.

Output

id | status | normalized | display_status
---+--------+------------+----------------
 1 | done   | done       | done
 2 | unset  |            | pending
 3 |        |            | pending

Why this works: NULLIF(status, 'unset') compares status to the literal 'unset' and returns NULL exactly when they match, which is why row 2's normalized column comes back blank even though its original status was the non-NULL text 'unset.' Once both row 2 and row 3 are genuinely NULL in the normalized column, COALESCE treats them identically and supplies the same 'pending' default for both — unifying two different representations of "no status" (a legacy sentinel and a true NULL) into one consistent NULL, then one consistent display value.

Using COALESCE alone when the source data mixes a sentinel value with real NULLs

Wrong

sql

SELECT COALESCE(status, 'pending') FROM tasks;
-- row 2's status is 'unset', a non-NULL string -- COALESCE has nothing to replace, sentinel passes through unchanged

Better

sql

SELECT COALESCE(NULLIF(status, 'unset'), 'pending') FROM tasks;
-- normalizes the sentinel to NULL first, so COALESCE catches it too

What you see: A default value correctly appears for genuinely NULL rows but not for rows using an older sentinel convention meaning the same thing, producing inconsistent output for what should be identical "no status" cases.

Why: COALESCE only replaces actual NULL values — it has no way to know that the string 'unset' is meant to represent the same absence of data that NULL represents, since 'unset' is a perfectly ordinary, valid text value as far as COALESCE is concerned. NULLIF is the tool that performs that normalization explicitly, converting the sentinel to a real NULL first so that COALESCE's NULL-replacement logic then applies to it correctly.

COALESCE vs NULLIF — opposite directions

COALESCE vs NULLIF — opposite directions
Function	Direction	Typical use
COALESCE(a, b, ...)	NULL → a real value	supply a default for a missing value
NULLIF(a, b)	a real value → NULL	treat a sentinel (like empty string or -1) as missing

Together

sql

SELECT COALESCE(nickname, first_name, 'Unknown') FROM users;
SELECT NULLIF(discount_code, '') FROM orders;

Remember: COALESCE returns the first non-NULL argument (NULL → a real value); NULLIF returns NULL when two values are equal (a real value → NULL) — chain them to normalize a legacy sentinel into NULL, then supply a default.

The trap and the discipline

SQL's best-known NULL pitfall, and designing a schema so NULL always means something specific.

NOT IN Pitfalls When NULL Values Exist

coreintermediate

NOT IN against any list — a literal list or a subquery result — that contains even one NULL always returns zero rows, silently, for every row in the outer query. This happens whether the NULL comes from a hardcoded list written by hand or from a subquery's result.

Think of it as

x NOT IN (a, b, NULL) is defined to expand into x <> a AND x <> b AND x <> NULL — and that last comparison, x <> NULL, is always NULL, which poisons the entire AND chain to NULL regardless of a and b. This is not specific to subqueries: the exact same failure happens with a plain, hand-typed literal list if a NULL literal (or a NULL-valued expression) ends up inside it.

sql

-- dangerous: a literal list containing NULL, not just a subquery
SELECT * FROM products WHERE category_id NOT IN (1, 2, NULL);
-- returns ZERO rows regardless of category_id's value

-- safe
SELECT * FROM products WHERE category_id NOT IN (1, 2)
   OR category_id IS NULL;

What we're doing: Reproduce the NOT IN + NULL failure using a plain hand-typed literal list, to show the pitfall is not exclusive to subqueries.

not_in_literal_null.sqlsql

CREATE TABLE products (id SERIAL PRIMARY KEY, category_id INT);
INSERT INTO products (category_id) VALUES (1), (2), (3), (NULL);

-- a developer hardcodes an "excluded categories" list, accidentally including a NULL
-- (perhaps copy-pasted from a query result that itself contained one)
SELECT * FROM products WHERE category_id NOT IN (1, 2, NULL);
-- expected: product with category_id = 3. actual: 0 rows.

SELECT * FROM products WHERE category_id NOT IN (1, 2);
-- no NULL in the list -- correctly returns category_id = 3

6: Even though NULL here is a hand-typed literal, not a subquery result, the same three-valued-logic rule applies identically.
9: Removing the NULL from the literal list restores the expected behavior — the failure was never about subqueries specifically.

Output

id | category_id
---+------------
(0 rows)

id | category_id
---+------------
 3 |           3
(1 row)

Why this works: The list (1, 2, NULL) is evaluated identically whether it came from typing it by hand or from a subquery — PostgreSQL has no separate code path for "a NULL that happens to be a literal" versus "a NULL that happens to come from a SELECT." The expansion to an AND chain, and the resulting NULL from the x <> NULL term, apply exactly the same way, which is why this pitfall is worth understanding as a property of NOT IN itself, not merely as a subquery-specific gotcha.

Assuming a hardcoded, hand-written NOT IN list is safe because it is not a subquery

Wrong

sql

SELECT * FROM products WHERE category_id NOT IN (1, 2, NULL);
-- looks like a simple, safe literal list -- it is not

Better

sql

SELECT * FROM products WHERE category_id NOT IN (1, 2);
-- if NULL genuinely needs excluding too, be explicit:
SELECT * FROM products WHERE category_id NOT IN (1, 2) AND category_id IS NOT NULL;

What you see: A query using a manually-typed exclusion list returns zero rows unexpectedly, and the cause is not obvious because there is no subquery to suspect — the NULL is sitting in plain sight in the literal list, easy to overlook.

Why: NOT IN's NULL sensitivity is a property of the operator itself, defined by the SQL standard's three-valued logic, and applies uniformly no matter where the list values come from — subquery, literal, or a mix. The practical lesson is to treat any NOT IN list as suspect whenever NULL could plausibly appear in it, whether typed by hand, pasted from a query result, or generated dynamically by application code.

Why NOT IN with a NULL in the list always returns unknown

x NOT IN (1, 2, NULL)

expands to an AND chain

x<>1 AND x<>2 AND x<>NULL

the last term is always NULL

true AND true AND NULL

AND with any NULL operand is NULL

Result: NULL

WHERE discards it — 0 rows, always

NOT IN vs IN when the list contains a NULL

NOT IN vs IN when the list contains a NULL
Expression	Result
5 IN (1, 2, NULL)	NULL (unknown) — but if 5 were actually in the list, e.g. 5 IN (1, 5, NULL), it would be true
5 NOT IN (1, 2, NULL)	NULL (unknown) — always, since NULL makes the AND chain unknown regardless of whether 5 matches 1 or 2
5 NOT IN (1, 2)	true — no NULL present, behaves as expected

Together

sql

SELECT 5 NOT IN (1, 2, NULL);   -- NULL, not true, even though 5 matches neither 1 nor 2

Remember: NOT IN against ANY list — literal or subquery — containing even one NULL always returns zero rows, silently. This is a property of the NOT IN operator itself, not something specific to subqueries.

Designing Schemas So NULL Means Meaningful Absence

standardintermediate

A NULL-able column should answer a specific question: "why might this legitimately have no value?" If there is no good answer — if every row should always have a value — the column should be NOT NULL, and any current gaps are a data problem to fix, not a schema feature to accommodate.

Think of it as

NULL-ability is a design decision with a specific claim attached, not a default relaxation. Every NULL-able column should have an answerable "why might this be missing" story — a shipping address for a digital-only product, a middle name nobody has, a cancellation date for an order that was never cancelled. A NULL-able column with no such story is usually a sign the schema is either missing a NOT NULL constraint that belongs there, or hiding a modeling problem (like several unrelated concerns crammed into one table) behind permissive nullability.

sql

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    total NUMERIC NOT NULL,
    shipped_at TIMESTAMPTZ,      -- legitimately absent until shipped
    cancelled_at TIMESTAMPTZ     -- legitimately absent unless cancelled
);

What we're doing: Attempt to add a NOT NULL constraint to a column and let PostgreSQL surface exactly which existing rows violate the intended invariant.

meaningful_null.sqlsql

CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);
INSERT INTO orders (total) VALUES (100), (50), (NULL);
-- the NULL total was meant to be filled in later but never was -- a data bug, not a real business state

SELECT id FROM orders WHERE total IS NULL;
-- find the bad rows before attempting the constraint

ALTER TABLE orders ALTER COLUMN total SET NOT NULL;
-- ERROR: column "total" contains null values

2: One order has NULL total — accidental incompleteness, not a legitimate "no total" business state.
5: Finding the offending rows first is the correct order of operations: fix the data, then add the constraint.
8: PostgreSQL refuses the constraint outright rather than silently allowing it alongside existing violations.

Output

 id
----
  3
(1 row)

ERROR:  column "total" of relation "orders" contains null values

Why this works: ALTER COLUMN ... SET NOT NULL checks every existing row before applying, and refuses outright if any row would violate the new constraint — PostgreSQL will not silently let the schema and the data disagree. This is exactly the mechanism that turns "total should never be missing" from a hopeful convention into an enforced guarantee: once the constraint is in place, no future INSERT or UPDATE can reintroduce the same accidental gap, and any reader of the schema can trust that total is always present without having to guess or defensively code around it.

Leaving a column NULL-able because enforcing NOT NULL feels inconvenient right now

Wrong

sql

CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);
-- NULL-able "to be safe," with no real business reason total should ever be missing

Better

sql

CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC NOT NULL);
-- forces every INSERT to supply a value, surfacing missing-data bugs immediately

What you see: Downstream code accumulates defensive NULL-checks and COALESCE calls scattered everywhere total is used, because nobody can be sure whether a given row has it — and occasionally a report is silently wrong because an aggregate quietly skipped the NULL rows.

Why: A NULL-able column with no legitimate absence case pushes the cost of ambiguity onto every future reader and every downstream query, each of which has to independently decide how to handle a NULL that should never have been possible in the first place. Enforcing NOT NULL at the schema level catches the problem exactly once, at write time, which is cheaper and more reliable than every consumer re-discovering and re-handling the same gap independently.

A NULL-able column, with and without an articulable reason

A NULL-able column, with and without an articulable reason
Column	NULL-able?	Why (or why not)
orders.shipped_at	yes	legitimately absent until the order actually ships
orders.cancelled_at	yes	legitimately absent for every order that was never cancelled
orders.customer_id	no	every order must belong to a customer — no legitimate absence case
orders.total (no clear reason to allow absence)	should be no	a missing total is a data bug, not a real business state

Together

sql

ALTER TABLE orders ALTER COLUMN total SET NOT NULL;
-- fails immediately if any existing row has total IS NULL -- surfacing the bug rather than hiding it

Remember: A NULL-able column should have a specific, articulable reason values can legitimately be absent — if there is no such reason, the column belongs as NOT NULL, and existing gaps are a data bug to fix, not a schema feature to accommodate.