NULL and NOT NULL

~12 min · schema, null

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

NULL is not zero, not empty, not false

NULL is the database's way of saying "I don't know." It is not the number zero. It is not the empty string. It is not the boolean false. It is the absence of a value — a blank space on a form that was never filled in.

NULL is contagious

Any operation involving NULL returns NULL. 5 + NULL is NULL. 'hi' || NULL is NULL. NULL = NULL is NULL (not true!). This is why every NULL check uses IS NULL / IS NOT NULL, never = NULL.

NOT NULL by default

Mark every column NOT NULL unless you genuinely need to represent the "I don't know" state. Allowing NULL where it isn't needed shifts the burden of "what if it's null?" onto every consumer of the data — and they will forget.

The IS DISTINCT FROM operator

If you need NULL-safe equality (NULL behaves as a regular value), use IS DISTINCT FROM and IS NOT DISTINCT FROM. They treat NULL = NULL as TRUE and yield FALSE elsewhere — predictable, scriptable, no three-valued logic.

Code

Schema-level NULL discipline·sql

CREATE TABLE contacts (
    id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name  TEXT NOT NULL,           -- always required
    phone TEXT,                    -- intentionally optional
    bio   TEXT NOT NULL DEFAULT '' -- always present, defaults to empty
);

NULL traps in WHERE·sql

-- WRONG: never matches anything (NULL = NULL is NULL, not TRUE)
SELECT * FROM contacts WHERE phone = NULL;

-- RIGHT
SELECT * FROM contacts WHERE phone IS NULL;
SELECT * FROM contacts WHERE phone IS NOT NULL;

-- NULL-safe equality
SELECT * FROM contacts WHERE phone IS NOT DISTINCT FROM '555-1212';

Aggregates silently skip NULL·sql

SELECT
    COUNT(*)         AS total_rows,
    COUNT(phone)     AS rows_with_phone,
    COUNT(DISTINCT phone) AS distinct_phones
FROM contacts;
-- COUNT(*) counts every row including NULLs.
-- COUNT(column) counts only non-NULL values.

External links

PostgreSQL NULL semantics

Exercise

Find a column in your project that is nullable but probably shouldn't be. Sketch the migration to add NOT NULL (you'll need to backfill existing NULLs first). Identify any code that would break if NULL became impossible there.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousSERIAL vs IDENTITY vs UUID Next →Default Values & Generated Columns

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.