COALESCE(a, b, c) returns the first of its arguments that is not NULL, so COALESCE(nickname, name) shows the nickname when there is one and the name otherwise. NULLIF(a, b) does the reverse: it returns NULL when a equals b. CASE WHEN condition THEN value ... ELSE value END picks a value by condition.
Ben and Chen have no nickname, so their full names appear. Ben also gets no phone.
Replace NULL with a default
COALESCE is PostgreSQL's IFNULL, ISNULL and NVL. Use it to turn NULL into 0 before arithmetic, since any arithmetic with NULL gives NULL, and to give an aggregate over zero rows a value:
Order 2 shows NULL without COALESCE and 80 with it, and the sum over no rows becomes 0 instead of NULL.
All arguments must resolve to one type. COALESCE(quantity, 'none') on an integer column fails with "invalid input syntax for type integer", because PostgreSQL tries to read 'none' as an integer. Cast the column first: COALESCE(quantity::text, 'none'). The CAST page covers casts.
Turn a value into NULL with NULLIF
NULLIF(a, b) returns NULL if a = b, and a otherwise. It prevents division by zero, and it turns empty strings into NULL so COALESCE can replace them:
spring gets a click rate of 5. summer gets NULL instead of a "division by zero" error, and its empty owner becomes unassigned.
Choose a value with CASE WHEN
This form tests conditions in order and returns the first match. Rows that match nothing get the ELSE value, or NULL when there is no ELSE:
The CASE in ORDER BY pushes sold-out products to the end, so mug comes last. A shorter form compares one expression with values: CASE status WHEN 'paid' THEN 1 WHEN 'refunded' THEN -1 ELSE 0 END. It cannot test for NULL, since NULL = NULL is not true. Use WHEN x IS NULL in the full form for that.
Count by condition
CASE inside an aggregate counts or sums by category in one pass. PostgreSQL also has the FILTER clause, which says the same thing more directly:
SELECT count(CASE WHEN status = 'paid' THEN 1 END) AS paid,
count(*) FILTER (WHERE status = 'paid') AS paid_too
FROM orders;
Both count only paid orders: count() skips the NULL that CASE returns for other rows.
GREATEST and LEAST ignore NULL
GREATEST(a, b, ...) and LEAST(a, b, ...) return the largest and smallest argument. In PostgreSQL they skip NULL arguments and return NULL only when every argument is NULL, so GREATEST(1, NULL, 3) is 3. MySQL returns NULL when any argument is NULL, which matters when you port queries. See the PostgreSQL docs on conditional expressions for the full rules.
Frequently Asked Questions
What is the PostgreSQL equivalent of IFNULL, ISNULL or NVL?
COALESCE(value, fallback). PostgreSQL has no IFNULL (MySQL), ISNULL (SQL Server) or NVL (Oracle) function; COALESCE is standard SQL and also takes more than two arguments.
How do I replace NULL with 0 in PostgreSQL?
Wrap the column: COALESCE(discount, 0). For a sum over no rows, which returns NULL, write COALESCE(SUM(amount), 0).
Does COALESCE evaluate all its arguments?
No. It evaluates the arguments in order and stops at the first one that is not NULL, so later arguments are not computed for that row.
Can I use CASE in a WHERE clause?
Yes, a CASE expression can appear anywhere a value can, including WHERE, ORDER BY and inside aggregates. In WHERE a plain AND/OR condition is usually easier to read.