Data types, NULL and three-valued logic

How the common column types behave, why = NULL never matches anything, and the COALESCE, NULLIF and CAST tools that keep missing data from corrupting results.

Types you will actually use

A column type decides what may be stored, how much space it takes, and which comparisons are legal. Storing everything as text works until you sort numbers alphabetically or compare a date that was written as a local string.

CREATE TABLE readings (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sensor    TEXT         NOT NULL,
  value     NUMERIC(8,2),                    -- exact: 0.1 + 0.2 stays 0.3
  taken_at  TIMESTAMPTZ  NOT NULL DEFAULT now(),
  active    BOOLEAN      NOT NULL DEFAULT TRUE,
  note      TEXT                             -- nullable on purpose
);

• Pick the narrowest type that cannot overflow for the lifetime of the data, not the widest one available.
• Text columns have no length limit in most engines unless you add one; a VARCHAR(255) habit is a habit, not a requirement.
• Timestamps should be stored in UTC with a zone-aware type, and converted to local time only for display.

NULL and three-valued logic

NULL means unknown, not empty and not zero. Any comparison involving it evaluates to UNKNOWN, and a row is returned only when the predicate is TRUE — which is why the most intuitive query returns nothing.

-- never true, never returns the rows you meant
SELECT * FROM users WHERE deleted_at = NULL;

-- both of these do work
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;

-- <> silently drops the NULL rows instead of reporting them
SELECT * FROM users WHERE status <> 'archived';

-- keep them on purpose
SELECT * FROM users WHERE status <> 'archived' OR status IS NULL;

-- null-safe comparison, spelled out
SELECT * FROM users WHERE status IS DISTINCT FROM 'archived';

• AND/OR follow Kleene logic: TRUE OR UNKNOWN is TRUE, FALSE AND UNKNOWN is FALSE, everything else touching UNKNOWN is UNKNOWN.
• NOT IN over a set that contains a NULL returns no rows at all — use NOT EXISTS instead.
• GROUP BY and DISTINCT treat all NULLs as one bucket, while a UNIQUE constraint normally allows repeated NULLs. Different contexts, different rules.
• Aggregates ignore NULLs rather than counting them, so AVG can be based on far fewer rows than you think.

Coalesce, nullif and casts

SELECT
  COALESCE(nickname, first_name, 'anonymous') AS display_name,
  COALESCE(discount, 0)                       AS discount,
  NULLIF(trim(email), '')                     AS email,      -- empty string becomes NULL
  CAST(price AS NUMERIC(10,2))                AS price,
  price::TEXT                                 AS price_text  -- PostgreSQL shorthand
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- counts rows and counts known values: two different numbers
SELECT COUNT(*) AS rows_seen, COUNT(phone) AS phones_known FROM users;

Decide once what a missing value means in each column and enforce it: either the column is NOT NULL with a real default, or it is nullable and every consumer knows to handle NULL. Mixing '' and NULL for the same idea is where most null bugs begin.

• Put COALESCE where the value is consumed, not in the table definition — the raw table should show you that data is missing.
• Casting is not conversion magic: casting a non-numeric string to an integer is an error at runtime, not a silent NULL.
• Comparing an integer column to a string literal often defeats the index; cast the constant, not the column.

FAQ

Related

Constraints, keys and normalisation Subqueries, CTEs and window functions

Last refreshed 2026-09-18.