PostgreSQL COALESCE: NULL values and practical defaults

PostgreSQL COALESCE returns the first non-NULL value in its argument list. If every argument is NULL, the result is NULL. It is useful for choosing a display label, supplying a fallback for a missing total, or combining optional fields, provided the arguments can resolve to a common data type.

The placement of that fallback matters. Replacing a missing amount with zero before calculating an average changes what the average measures. Supplying a label in a SELECT does not update the stored row. This guide uses small examples to make those differences visible.

We ran 32 checks on isolated PostgreSQL 18.6. The outputs below were captured with psql in unaligned, tuples-only mode: columns are separated by |, booleans appear as t or f, and SQL NULL is displayed as <NULL>.

Start with the first non-NULL value

SELECT COALESCE(NULL::text, 'fallback', 'later') AS chosen,
       COALESCE(0, 99) AS zero_value,
       COALESCE(false, true) AS boolean_value,
       COALESCE(NULL::text, NULL::text) IS NULL AS all_null;

fallback|0|f|t

The first expression skips NULL and returns fallback. Zero and false are retained because both are actual values. The final expression confirms that two typed NULL arguments still produce NULL.

Use COALESCE when the decision is simply which available value to choose. Use CASE when the choice depends on another condition, such as a status or a numeric threshold. The PostgreSQL conditional-expression reference documents both forms. COALESCE resembles a function call, but PostgreSQL treats it as a conditional expression.

Distinguish NULL, empty strings, and spaces

Create these test objects in a disposable database, using a fresh schema name:

CREATE SCHEMA coalesce_lab;
CREATE TABLE coalesce_lab.contacts (
    id integer PRIMARY KEY,
    display_name text,
    legal_name text,
    amount numeric
);
INSERT INTO coalesce_lab.contacts VALUES
    (1, NULL, 'Ada', 10),
    (2, '', 'Bea', NULL),
    (3, NULL, NULL, 20);

The second row has an empty display name. An empty string is not NULL, so it prevents a plain COALESCE from reaching the legal name. Compare that result with an explicit normalization rule:

SELECT id,
       quote_nullable(COALESCE(display_name, legal_name, '[unnamed]')) AS raw_label,
       quote_nullable(COALESCE(NULLIF(btrim(display_name), ''),
                               legal_name, '[unnamed]')) AS normalized_label
FROM coalesce_lab.contacts
ORDER BY id;

1|'Ada'|'Ada'
2|''|'Bea'
3|'[unnamed]'|'[unnamed]'

quote_nullable makes the text values visible: '' is an empty string, while the single quotes around Ada are display formatting. The normalized expression trims ordinary leading and trailing spaces, turns an empty result into NULL with NULLIF, and then considers the legal name.

That is a deliberate rule for this display-name field. It also changes ' Ada ' to 'Ada', which our checks confirmed. Do not apply it blindly to identifiers or fields where spaces are meaningful. A separate space-only test returned the fallback after normalization; plain COALESCE preserved all three spaces. This is not a general Unicode-whitespace cleaning routine.

The third row has neither name, so both expressions use the placeholder. Neither SELECT changes the stored values. The two original NULL display names remain NULL after the queries.

Choose a compatible result type

SELECT COALESCE(NULL::integer, 2.5::numeric) AS chosen,
       pg_typeof(COALESCE(NULL::integer, 2.5::numeric)) AS result_type;
SELECT COALESCE(NULL::integer::text, 'unknown'::text) AS display_label;

2.5|numeric
unknown

The first query combines an integer argument with a numeric argument and produces numeric. The second explicitly converts the nullable integer to text before providing a text label. This keeps presentation choices separate from numeric calculations.

Two useful failure cases came from the test run. COALESCE(1, 'unknown') failed with SQLSTATE 22P02 because the literal could not be converted to integer. Making the second argument explicitly text, as in COALESCE(1, 'unknown'::text), failed with 42804 because the types could not be matched. Having a non-NULL first argument did not bypass type checking.

A compatible literal can work: COALESCE(NULL::integer, '42') returned 42 in our check. Still, explicit casts make the intended type easier to review. PostgreSQL’s type-resolution rules explain how related expressions select a common type. Use numeric defaults for calculations and text defaults for labels.

Place the fallback around the right aggregate

SELECT count(*) AS rows_seen,
       count(amount) AS known_amounts,
       sum(amount) AS total,
       round(avg(amount), 2) AS average_known,
       round(avg(COALESCE(amount, 0)), 2) AS average_with_zero
FROM coalesce_lab.contacts;
SELECT COALESCE(sum(amount), 0) AS default_after_sum,
       sum(COALESCE(amount, 0)) AS default_before_sum
FROM coalesce_lab.contacts
WHERE id < 0;

3|2|30|15.00|10.00
0|<NULL>

The three rows contain two known amounts, 10 and 20. Their total is 30 and their average is 15. Replacing the missing amount with zero before AVG creates three numeric inputs, so the average becomes 10. That result may be appropriate only if the missing amount actually means zero.

The second query has no input rows. Defaulting the SUM result produces zero, while putting COALESCE inside SUM still produces NULL: there are no rows on which the inner expression can run. The aggregate-function reference describes the empty-input behavior.

Our additional checks separated three cases. On the single row whose amount is NULL, both sum expressions returned zero. On no rows, COUNT(*) and COUNT(amount) both returned zero. Adding GROUP BY to an empty input produced no group at all, so COALESCE could not invent a missing category. If a report needs every category or calendar date, supply those rows separately.

For this fixture, COALESCE(AVG(amount), 0) kept the average at 15 because it applies the fallback after aggregation. Choose whether NULL means unknown, not applicable, or a genuine zero before changing the denominator.

Keep display fallbacks separate from column defaults

CREATE TABLE coalesce_lab.preferences (
    id integer PRIMARY KEY,
    label text DEFAULT 'standard'
);
INSERT INTO coalesce_lab.preferences (id) VALUES (1);
INSERT INTO coalesce_lab.preferences (id, label) VALUES (2, NULL);
SELECT id, quote_nullable(label) AS stored,
       COALESCE(label, 'display fallback') AS displayed
FROM coalesce_lab.preferences
ORDER BY id;

1|'standard'|standard
2|NULL|display fallback

Omitting the label in the first INSERT uses the column default. Explicitly inserting NULL in the second INSERT stores NULL. COALESCE supplies a label when reading that second row, but the stored value remains NULL.

A display fallback therefore does not enforce a data rule. If a column must always contain a value, decide on suitable write behavior and constraints. If existing records need correction, plan a separate, reviewed data change rather than assuming a SELECT has cleaned them.

Notice when a filter includes missing values

WITH numbers(id, value) AS (
    VALUES (1, NULL::integer), (2, 0), (3, 5)
)
SELECT id, (value = 0) AS equals_zero,
       (COALESCE(value, 0) = 0) AS null_as_zero,
       (value IS NULL) AS missing
FROM numbers
ORDER BY id;

1|<NULL>|t|t
2|t|t|f
3|f|f|f

For the missing value, value = 0 evaluates to SQL NULL, shown as <NULL>. A WHERE clause keeps rows whose condition is true. In the checks, plain equality selected only the known zero; COALESCE(value, 0) = 0 selected both the missing value and the known zero. IS NULL selected only the missing value.

Use the NULL comparison predicates when missingness itself is the condition. A fallback in a filter expresses a policy about which rows belong in the result; it should not be added merely to remove NULL from the output.

We did not benchmark these small examples or test an index recommendation. If changing a filter affects a production query, inspect the actual plan with the PostgreSQL EXPLAIN ANALYZE guide. EXPLAIN ANALYZE executes the query, so use it with the appropriate care for statements that change data.

Test fallback evaluation without relying on errors

A test sequence provides an observable fallback. It belongs only in this disposable exercise:

CREATE SEQUENCE coalesce_lab.probe START 1;
SELECT COALESCE(7::bigint, nextval('coalesce_lab.probe')) AS chosen;
SELECT is_called FROM coalesce_lab.probe;
SELECT COALESCE(NULL::bigint, nextval('coalesce_lab.probe')) AS fallback;
SELECT is_called FROM coalesce_lab.probe;

7
f
1
t

The first call returns 7, and the sequence still reports that it has not been called. The NULL first argument in the next call reaches nextval, returns 1, and changes is_called to true. A later zero-first test also left the sequence value unchanged.

This demonstrates skipped evaluation of the unused fallback for these valid expressions. It does not make invalid types acceptable, as the earlier error tests showed. Keep side effects out of ordinary display expressions; the sequence is a test probe, not a recommended application pattern.

Clean up and choose the fallback deliberately

DROP SEQUENCE coalesce_lab.probe;
DROP TABLE coalesce_lab.preferences;
DROP TABLE coalesce_lab.contacts;
DROP SCHEMA coalesce_lab;

Our checks confirmed that the sample rows were unchanged before cleanup, removed the exercise objects, and stopped the isolated server. For your own query, verify the result type, the distinction between missing and empty values, and whether defaulting happens before or after aggregation. Include an all-NULL case and a no-row case; they answer different questions.