NULL Semantics
NULL means unknown, not zero or empty string. Comparisons yield NULL (unknown). Use IS NULL, IS NOT NULL, and IS DISTINCT FROM for safe equality.
//
Search across all documentation pages
NULL means unknown, not zero or empty string. Comparisons yield NULL (unknown). Use IS NULL, IS NOT NULL, and IS DISTINCT FROM for safe equality.
SELECT * FROM app.customers WHERE phone IS NULL;
SELECT * FROM app.customers
WHERE status IS DISTINCT FROM 'active';
SELECT coalesce(phone, 'unknown') AS phone_display FROM app.customers;When to reach for this: This pattern appears in application or reporting SQL you maintain.
CREATE TABLE app.status_demo (a text, b text);
INSERT INTO app.status_demo VALUES ('x', NULL), (NULL, NULL);
SELECT a, b, (a = b) AS eq, (a IS NOT DISTINCT FROM b) AS not_distinct
FROM app.status_demo;What this demonstrates:
| Alternative | Use When | Don't Use When |
|---|---|---|
| ORM query builder | Team standardizes on one stack | Complex SQL becomes opaque |
| Materialized view | Repeat expensive aggregates | Needs refresh strategy |
| Warehouse replica | Heavy BI scans | Not for OLTP latency |
Use multiline template strings or SQL files; lint in CI.
When you need unique rows without aggregates.
Sort can use index order if query matches index columns.
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.
Reviewed by Chris St. John·Last updated Jul 18, 2026