Study notes · 10% of the exam

DML, Operators and NULL Logic

Read and change rows correctly: know the logical order of a SELECT, how NULL flows through comparisons and operators, and how INSERT, UPDATE, DELETE, upserts and MERGE really behave in PostgreSQL.

Key points

1
Logical order is FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. WHERE can't see SELECT aliases; a bare alias works in ORDER BY.
2
Comparisons with NULL are unknown, and WHERE keeps only TRUE rows. Test with IS NULL, and use IS DISTINCT FROM for NULL-safe (in)equality.
3
NOT IN with a NULL in the list (or subquery) returns no rows; prefer NOT EXISTS.
4
Most operators are strict (NULL in → NULL out): ||, arithmetic, LIKE. concat() skips NULLs; COALESCE picks the first non-NULL; x / NULLIF(y, 0) avoids division by zero.
5
CASE returns the first true branch and defaults to NULL without ELSE; a simple CASE x WHEN NULL never matches. COALESCE and CASE must resolve to one data type.
6
Upserts: ON CONFLICT … DO UPDATE uses EXCLUDED for the proposed row and can't touch the same row twice in one statement; DO NOTHING skips silently and RETURNING omits skipped rows.
7
An UPDATE with a scalar subquery sets NULL where nothing matches; UPDATE … FROM uses only one of several matching join rows, unpredictably.

Common traps

BETWEEN '2024-01-01' AND '2024-01-31' on a timestamp stops at midnight on the 31st; use >= start AND < next_day.
An explicit NULL in an INSERT overrides the column default; only an omitted column or the DEFAULT keyword uses it.
Without a unique tiebreaker in ORDER BY, LIMIT/OFFSET pages can repeat or skip rows.

Read the source

Test yourself on DML, Operators and NULL Logic

Ten questions, with the answer and explanation after each one.