❓

PostgreSQL Conditional Expressions — CASE, COALESCE, NULLIF

Complete reference for PostgreSQL conditional expressions covering CASE, COALESCE, NULLIF, GREATEST, LEAST, and NULL-safe comparison patterns. Every expression includes syntax, real SQL examples, and common mistakes when handling NULL values in PostgreSQL. Updated for PostgreSQL 16.

7 functions

Jump to function

CASE COALESCE NULLIF GREATEST LEAST IS NULL / IS NOT NULL IS DISTINCT FROM / IS NOT DISTINCT FROM

What are PostgreSQL Conditional Expressions — CASE, COALESCE, NULLIF?

PostgreSQL conditional expressions are built-in constructs for branching logic and NULL handling in SQL queries. CASE expressions work like if-else statements, COALESCE returns the first non-NULL value in a list, and NULLIF returns NULL when two expressions are equal. These are essential for data cleaning, default value substitution, and NULL-safe comparisons throughout PostgreSQL development.

CASE

PG 7.4+→ common type of all result expressions

General conditional expression. Evaluates conditions in order and returns the result for the first true condition, or the ELSE value if none match.

DeveloperData Eng

Signatures

CASE WHEN condition THEN result [WHEN ...] [ELSE default] END

CASE expression WHEN value THEN result [WHEN ...] [ELSE default] END

Parameters

Parameter	Type	Description
condition	boolean	Each WHEN clause is evaluated in order
result	any	Value returned when the corresponding condition is true
default	any	Value returned if no condition matches; NULL if ELSE is omitted

Examples

sql

SELECT CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' WHEN score >= 70 THEN 'C' ELSE 'F' END AS grade FROM results;

→Letter grade

sql

SELECT CASE status WHEN 'active' THEN 1 WHEN 'paused' THEN 2 ELSE 0 END AS status_code FROM accounts;

→Simple switch-style case

sql

SELECT region, SUM(CASE WHEN returned = true THEN 1 ELSE 0 END) AS returns, COUNT(*) AS total FROM orders GROUP BY region;

→Return count vs total per region (conditional pivot)

sql

UPDATE products SET tier = CASE WHEN price < 10 THEN 'budget' WHEN price < 50 THEN 'mid' ELSE 'premium' END;

→Bulk classification update using CASE in SET clause

⚠Anti-Pattern— Deeply nested CASE expressions

Nesting CASE 4+ levels deep (CASE WHEN … THEN CASE WHEN … THEN CASE …) creates unmaintainable logic that is nearly impossible to debug. Each additional level exponentially increases cognitive load.

✓ Instead: Refactor into a lookup/reference table joined at query time, a PL/pgSQL function, or multiple CTEs that classify data incrementally. For simple NULL substitution, prefer COALESCE over a CASE WHEN col IS NULL THEN default ELSE col END pattern.

Combine CASE with COUNT or SUM to pivot or count conditionally: `SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END)` counts paid-only totals within a GROUP BY — no subquery needed.

example

SELECT month, SUM(CASE WHEN type = 'income' THEN amount ELSE 0 END) AS income, SUM(CASE WHEN type = 'expense' THEN amount ELSE 0 END) AS expenses FROM ledger GROUP BY month;

→Conditional sums per category per month

COALESCE

PG 7.4+→ common type of all arguments

Returns the first non-NULL argument. Evaluates arguments left-to-right and stops at the first non-NULL value.

DeveloperData Eng

Signature

COALESCE ( value [, value ...] ) → same as first non-null type

Parameters

Parameter	Type	Description
value	any (all must be compatible types)	Arguments evaluated in order; first non-NULL is returned

Examples

sql

SELECT COALESCE(phone, mobile, 'N/A') FROM contacts;

→First non-NULL phone number, or 'N/A'

sql

SELECT COALESCE(nickname, first_name) AS display_name FROM users;

→Nickname if set, otherwise first name

sql

SELECT COALESCE(sum(amount), 0) FROM payments WHERE user_id = 999;

→0 instead of NULL for no rows

sql

INSERT INTO events (label) SELECT COALESCE(custom_label, category_name, 'Uncategorized') FROM raw_events;

→ETL load with a three-level fallback label hierarchy

⚠Anti-Pattern— COALESCE(col, 0) masking NULLs in aggregates

Wrapping a column with COALESCE(col, 0) before passing it to AVG() or SUM() silently converts missing data to zeros, skewing results. NULL in aggregate functions is intentionally excluded from calculations.

✓ Instead: Let aggregate functions handle NULLs naturally (they ignore them). Apply COALESCE only to the aggregate result if you need a zero when no rows match: `COALESCE(AVG(col), 0)` rather than `AVG(COALESCE(col, 0))`.

COALESCE stops as soon as it finds a non-NULL value — it does not evaluate subsequent arguments. This means `COALESCE(expensive_func(), fallback)` only calls `expensive_func()` until it returns non-NULL.

example

SELECT COALESCE(cached_value, compute_expensive_value(id)) FROM items;

→Returns cached_value if available, only computes expensive value when needed

NULLIF

PG 7.4+→ same as value1

Returns NULL if value1 equals value2, otherwise returns value1. The inverse of COALESCE.

DeveloperDBA

Signature

NULLIF ( value1, value2 ) → same as value1 or NULL

Parameters

Parameter	Type	Description
value1	any	Value to return if not equal to value2
value2	any (compatible type)	Sentinel value that triggers NULL return

Examples

sql

SELECT NULLIF(0, 0) AS result;

→NULL

sql

SELECT 100 / NULLIF(denominator, 0) FROM calculations;

→NULL instead of division-by-zero error

sql

SELECT NULLIF(status, 'N/A') FROM records;

→NULL where status is 'N/A'

sql

UPDATE imports SET notes = NULLIF(TRIM(notes), '') WHERE batch_id = $1;

→Converts whitespace-only strings to NULL during data quality cleanup

⚠Anti-Pattern— Using NULLIF(0, 0) as a constant NULL

Writing NULLIF(0, 0) as a shorthand way to produce a NULL literal in queries or column defaults is confusing and misleading. It obscures intent and relies on a side effect of the function's primary purpose.

✓ Instead: Use the NULL keyword directly (e.g. SELECT NULL::integer) when you need an explicit NULL value. Reserve NULLIF for its intended use: converting a specific sentinel value in a column or expression into NULL.

Use `NULLIF(denominator, 0)` to turn zero denominators into NULL, which propagates through division to give NULL instead of an error. Combine with COALESCE to substitute a fallback: `COALESCE(numerator / NULLIF(denominator, 0), 0)`.

example

SELECT event_name, COALESCE(conversions::numeric / NULLIF(impressions, 0) * 100, 0) AS ctr_pct FROM ad_stats;

→Click-through rate, 0 when no impressions

GREATEST

PG 8.0+→ common type of all arguments

Returns the largest value among the provided arguments, ignoring NULLs. Returns NULL only if all arguments are NULL.

Developer

Signature

GREATEST ( value [, value ...] ) → same as arguments

Parameters

Parameter	Type	Description
value	any sortable type (all must be compatible)	Values to compare

Examples

sql

SELECT GREATEST(1, 5, 3, 2);

→5

sql

SELECT GREATEST(price, minimum_price) AS effective_price FROM products;

→Enforces a price floor

sql

SELECT GREATEST(date1, date2, date3) AS latest_date FROM events;

→Most recent of three dates

sql

SELECT user_id, GREATEST(last_login, last_purchase, last_comment) AS last_activity FROM user_stats;

→Most recent activity timestamp across three event columns

⚠Anti-Pattern— Expecting GREATEST/LEAST to behave like MAX/MIN with NULLs

GREATEST and LEAST silently ignore NULL arguments and return the largest/smallest non-NULL value. This differs from SQL aggregate MAX/MIN, but can still surprise: if all but one argument are NULL, you get that single value with no warning that data was missing.

✓ Instead: If NULLs in any column should cause the result to be NULL (strict semantics), add an explicit IS NULL guard: `CASE WHEN col1 IS NULL OR col2 IS NULL THEN NULL ELSE GREATEST(col1, col2) END`. Use aggregate MAX/MIN with GROUP BY when working across rows rather than across columns.

Clamp a value to a range with `GREATEST(min_val, LEAST(value, max_val))`. This is cleaner than a CASE expression for bounded values.

example

SELECT GREATEST(0, LEAST(rating, 5)) AS clamped_rating FROM reviews;

→Rating clamped to range [0, 5]

LEAST

PG 8.0+→ common type of all arguments

Returns the smallest value among the provided arguments, ignoring NULLs.

Developer

Signature

LEAST ( value [, value ...] ) → same as arguments

Parameters

Parameter	Type	Description
value	any sortable type (all must be compatible)	Values to compare

Examples

sql

SELECT LEAST(10, 5, 8, 2);

→2

sql

SELECT LEAST(deadline, max_date) AS effective_deadline FROM tasks;

→Earlier of two deadlines

sql

SELECT LEAST(CURRENT_DATE + 30, expires_at) AS offer_expiry FROM offers;

→Soonest of 30 days or stated expiry

sql

SELECT order_id, LEAST(quantity_requested, stock_available) AS fulfillable_qty FROM order_lines JOIN inventory USING (sku);

→Quantity that can actually be fulfilled given current stock

⚠Anti-Pattern— Expecting LEAST/GREATEST to propagate NULLs like arithmetic operators

Unlike arithmetic (e.g. 5 + NULL = NULL), LEAST(5, NULL) returns 5, not NULL. Developers familiar with NULL propagation rules are often surprised that a NULL argument is simply skipped rather than making the whole result NULL.

✓ Instead: If any NULL input should produce a NULL result, check inputs first: `CASE WHEN col1 IS NULL OR col2 IS NULL THEN NULL ELSE LEAST(col1, col2) END`. Document this behavior explicitly when the function is used in data quality or SLA calculations where a missing value is meaningfully different from a low value.

Use `LEAST(value, cap)` to cap an upper bound: `LEAST(discount, max_discount)` ensures the discount never exceeds the cap. Cleaner than `CASE WHEN discount > max_discount THEN max_discount ELSE discount END`.

example

SELECT user_id, LEAST(points_earned, 1000) AS capped_points FROM loyalty_events;

→Points capped at 1000

IS NULL / IS NOT NULL

PG 7.4+→ boolean

Tests whether a value is NULL or not NULL. Always returns true or false, even for NULL input (unlike = NULL which always returns NULL).

DeveloperDBA

Signatures

expression IS NULL → boolean

expression IS NOT NULL → boolean

Parameters

Parameter	Type	Description
expression	any	Value to test for nullness

Examples

sql

SELECT * FROM users WHERE email IS NULL;

→Users with no email

sql

SELECT * FROM orders WHERE shipped_at IS NOT NULL;

→Orders that have been shipped

sql

SELECT count(*) FILTER (WHERE phone IS NULL) AS missing_phone FROM contacts;

→Count of records with no phone

sql

SELECT column_name FROM information_schema.columns WHERE table_name = 'orders' AND column_default IS NULL AND is_nullable = 'NO';

→Required columns with no default — helpful for schema audits

⚠Anti-Pattern— Using = NULL or <> NULL in WHERE clauses

Writing `WHERE col = NULL` or `WHERE col <> NULL` is a very common mistake. Both expressions always evaluate to NULL (not TRUE or FALSE), so the WHERE clause never matches any row — silently returning zero rows or all rows depending on context.

✓ Instead: Always use `IS NULL` and `IS NOT NULL` for NULL testing. If you need null-safe equality between two columns that might both be NULL, use `IS NOT DISTINCT FROM` instead of `=`.

The comparison `col = NULL` always evaluates to NULL (unknown), never TRUE. You must use `IS NULL` to test for nullness. This catches many beginners by surprise.

example

-- Wrong: WHERE deleted_at = NULL (returns nothing)
-- Correct:
SELECT * FROM records WHERE deleted_at IS NULL;

→Active (non-deleted) records

IS DISTINCT FROM / IS NOT DISTINCT FROM

PG 7.4+→ boolean

Null-safe equality comparison. IS DISTINCT FROM returns true if values differ or one is NULL (treating NULL as a known value, not unknown).

DeveloperDBA

Signatures

value1 IS DISTINCT FROM value2 → boolean

value1 IS NOT DISTINCT FROM value2 → boolean

Parameters

Parameter	Type	Description
value1	any	Left-hand comparison operand
value2	any	Right-hand comparison operand

Examples

sql

SELECT NULL IS DISTINCT FROM NULL;

→false (they're equal)

sql

SELECT NULL IS DISTINCT FROM 1;

→true (they differ)

sql

SELECT * FROM t WHERE old_value IS DISTINCT FROM new_value;

→Rows where value actually changed (including NULL transitions)

sql

SELECT * FROM config_audit WHERE new_setting IS NOT DISTINCT FROM old_setting;

→Audit rows where the setting did not actually change, including rows where both old and new are NULL

⚠Anti-Pattern— Using <> for change detection when NULLs are possible

Using `old_value <> new_value` in audit logic or trigger conditions silently misses changes to or from NULL. When either side is NULL, the expression evaluates to NULL (not TRUE), so the change goes undetected.

✓ Instead: Use `old_value IS DISTINCT FROM new_value` for any change detection that involves nullable columns. This is especially important in audit triggers, CDC pipelines, and conditional update logic.

In UPDATE triggers, `NEW.col IS DISTINCT FROM OLD.col` detects real changes including NULL transitions. Regular `NEW.col <> OLD.col` misses cases where one side is NULL.

example

IF NEW.email IS DISTINCT FROM OLD.email THEN -- email actually changed END IF;

→Correctly detects change from NULL to value, value to NULL, or value to different value

PostgreSQL Conditional Expressions — CASE, COALESCE, NULLIF

Jump to function

What are PostgreSQL Conditional Expressions — CASE, COALESCE, NULLIF?

CASE

Signatures

Parameters

Examples

COALESCE

Signature

Parameters

Examples

NULLIF

Signature

Parameters

Examples

GREATEST

Signature

Parameters

Examples

LEAST

Signature

Parameters

Examples

IS NULL / IS NOT NULL

Signatures

Parameters

Examples

IS DISTINCT FROM / IS NOT DISTINCT FROM

Signatures

Parameters

Examples

Related PostgreSQL Categories