The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Test for null | WHERE deleted_at IS NULL | View examples |
| Test for a value | WHERE email IS NOT NULL | View examples |
| Null-safe inequality | old_value IS DISTINCT FROM new_value | View examples |
| Null-safe equality | left_value IS NOT DISTINCT FROM right_value | View examples |
| First non-null value | COALESCE(display_name, username, 'Anonymous') | View examples |
| Convert equality to null | NULLIF(denominator, 0) | View examples |
| Searched CASE | CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END | View examples |
| Simple CASE | CASE status WHEN 'open' THEN 1 WHEN 'closed' THEN 0 ELSE NULL END | View examples |
| Count non-null values | COUNT(completed_at) | View examples |
| Count all rows | COUNT(*) | View examples |
| Place nulls last | ORDER BY priority DESC NULLS LAST | View examples |
NULL represents an unknown or absent value, not an empty string or zero. Comparisons involving NULL usually produce unknown, and WHERE retains only true rows. Use null-specific predicates and make fallback, equality, aggregation, and ordering behavior explicit.
Step by step
Detailed examples
Filter three-valued logic intentionally
`value = NULL` is unknown, never true; use IS NULL. AND, OR, and NOT propagate unknown according to three-valued logic, and CHECK accepts true or unknown unless a separate NOT NULL constraint rejects absence. Parenthesize mixed conditions.
WITH items (id, deleted_at) AS (VALUES (1, NULL::timestamp), (2, TIMESTAMP '2026-01-01'))
SELECT id FROM items WHERE deleted_at IS NULL ORDER BY id;id
--
1Compare nullable values with boolean results
Ordinary equality and inequality return unknown when either side is null. IS DISTINCT FROM and IS NOT DISTINCT FROM always return true or false and are useful for change detection, joins, and tests where null should behave as a value.
SELECT NULL IS DISTINCT FROM NULL AS changed_a,
NULL IS DISTINCT FROM 3 AS changed_b,
3 IS NOT DISTINCT FROM 3 AS equal_c;changed_a | changed_b | equal_c
----------+-----------+--------
false | true | trueFallback or neutralize values lazily
COALESCE evaluates only until it finds a non-null result and requires compatible result types. NULLIF is useful for turning a sentinel or zero denominator into null. A fallback changes presentation or calculation semantics; it does not repair missing source data.
SELECT COALESCE(NULL, 'guest') AS label,
10 / NULLIF(0, 0) AS ratio;label | ratio
------+------
guest | NULLReturn one typed result from ordered branches
Searched CASE tests arbitrary conditions; simple CASE compares one expression to values. The first matching branch wins, so put specific conditions first. CASE normally evaluates only the chosen result, but planning-time constant folding can still expose invalid constant expressions.
WITH scores(value) AS (VALUES (95), (84), (NULL))
SELECT value, CASE WHEN value >= 90 THEN 'A' WHEN value >= 80 THEN 'B' ELSE 'ungraded' END AS grade
FROM scores ORDER BY value NULLS LAST;value | grade
------+---------
84 | B
95 | A
NULL | ungradedSpecify null behavior in summaries and sort order
Most aggregates ignore null inputs; COUNT(*) counts rows and COUNT(expression) counts non-null results. An aggregate over no qualifying inputs often returns null, except COUNT. PostgreSQL defaults null placement based on direction, so state NULLS FIRST or LAST when presentation matters.
WITH values(value) AS (VALUES (1), (NULL), (3))
SELECT COUNT(*) AS rows, COUNT(value) AS known, AVG(value) AS average FROM values;rows | known | average
-----+-------+--------
3 | 2 | 2.0000Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.