SQL COALESCE: Typed Defaults Without Hiding Missing Data

COALESCE supplies a fallback; it does not establish missing facts

COALESCE returns the first non-NULL expression from left to right, after the expressions have been resolved to a compatible result type. It is useful for display defaults and explicitly defined absence policies. It cannot prove that an unknown stock count, amount, rate, or measurement equals zero. These PostgreSQL 16 examples keep that distinction visible.

Separate NULL, blank text, and observed zero

A blank string is not NULL in PostgreSQL. Neither is a numeric zero. Decide whether a blank display name should count as missing before using NULLIF and trimming. A fallback expression affects the query result; it does not repair the original source record or validate a name’s identity.

Choose a display label without changing identity

WITH users(id,nickname,full_name) AS (
 VALUES(1,' Ada ','Ada Lovelace'),(2,'  ','Ben Fox'),(3,NULL,NULL),(4,'0','Zero Label')
)
SELECT id,COALESCE(NULLIF(BTRIM(nickname),''),full_name,'Name unavailable') AS display_name
FROM users ORDER BY id;

id | display_name
1 | Ada
2 | Ben Fox
3 | Name unavailable
4 | 0

NULLIF converts an empty trimmed nickname to NULL under this display policy. COALESCE then tries full_name and finally a label. The id remains the identity; several users can legitimately receive “Name unavailable.” Do not group or merge those people by the fallback label.

Keep a typed date separate from a human status

WITH orders(id) AS (VALUES(1),(2)),
shipments(order_id,shipped_on) AS (VALUES(1,DATE '2026-01-10'))
SELECT o.id,s.shipped_on,
 CASE WHEN s.order_id IS NULL THEN 'not shipped' ELSE 'shipped' END AS status,
 COALESCE(TO_CHAR(s.shipped_on,'YYYY-MM-DD'),'Date unavailable') AS display_date
FROM orders o LEFT JOIN shipments s ON s.order_id=o.id ORDER BY o.id;

id | shipped_on | status | display_date
1 | 2026-01-10 | shipped | 2026-01-10
2 | NULL | not shipped | Date unavailable

This fixture permits at most one shipment per order, and a real shipment has a known date. A real dataset with multiple shipments needs a chosen output grain. COALESCE(date_column, “Pending”) is not a valid typed-date fallback in PostgreSQL because Pending cannot be converted to a date. Keep the date column for computation and a text display field for presentation. The first non-NULL branch does not determine the data type at runtime.

Defaulting before an aggregate changes its input

WITH readings(value) AS (VALUES(10::numeric),(NULL),(0))
SELECT COUNT(*) AS rows,COUNT(value) AS observed,
 AVG(value) AS observed_average,AVG(COALESCE(value,0)) AS imputed_average,
 COALESCE(AVG(value),0) AS defaulted_result
FROM readings;

rows | observed | observed_average | imputed_average | defaulted_result
3 | 2 | 5 | 3.333... | 5

The observed mean is (10+0)/2 = 5. Replacing the missing reading with zero before AVG changes the denominator to three and the mean to 3.333…. Defaulting the final AVG only acts when the aggregate is NULL. A fallback after aggregation can still misrepresent unknown data; it is appropriate only when the defined result for that absence is really zero.

WITH readings(value) AS (SELECT NULL::numeric WHERE false)
SELECT SUM(COALESCE(value,0)) AS inside_sum,
 COALESCE(SUM(value),0) AS outside_sum FROM readings;

inside_sum | outside_sum
NULL | 0

There are no input rows on which the inner COALESCE could run. SUM still returns NULL. The outer COALESCE replaces that result. This difference matters in reports for populations with no events, and is distinct from a nonempty set of unknown amounts.

Retain unknown amounts when they change the decision

WITH amounts(group_id,value) AS (VALUES('A',10::numeric),('A',NULL),('B',0))
SELECT group_id,COUNT(*) AS rows,COUNT(value) AS known,
 CASE WHEN COUNT(*)=COUNT(value) THEN SUM(value) END AS complete_total
FROM amounts GROUP BY group_id ORDER BY group_id;

group_id | rows | known | complete_total
A | 2 | 1 | NULL
B | 1 | 1 | 0

A has an incomplete total; B has a known zero. This contract is more informative than forcing every total to a number. In inventory, a missing count might mean a stale feed, not a stockout. In feedback, imputing the mean changes variance and can bias an analysis. Any imputation needs an explicit modeling rule and a retained missingness indicator.

Use compatible types and inspect predicates

PostgreSQL resolves compatible COALESCE argument types before choosing a value. Cast deliberately when a display string is intended; do not coerce operational date or numeric columns to text just to make an error disappear. SQL Server ISNULL and MySQL IFNULL/ISNULL are dialect-specific constructs with different meanings or typing rules, so do not substitute by name alone.

Wrapping an indexed column in COALESCE can change the expression available to the planner, but does not prove an index is unusable in every possible query. Measure the actual predicate and plan. Avoid predicates that treat NULL and zero as equal unless that is genuinely the matching rule. COALESCE is a value expression, not parameter validation or authorization.

Explain what the fallback means

A good explanation says “this is a display label,” “absence means no events in a complete feed,” or “the amount remains unknown.” It does not claim the fallback recovered missing evidence. For the broader aggregate denominator rules, see NULLs and aggregates. Primary references: COALESCE and NULLIF, type resolution, and empty aggregates.

FAQ

Does COALESCE replace empty strings? Not by itself in PostgreSQL. Does it alter the table? A SELECT fallback does not. Can all its arguments be NULL? Yes; the result is NULL when no non-NULL argument exists. Should every numeric NULL become zero? Only if the stated data contract supports that meaning.