Sign in Get started

PostgreSQL 18 reference · Conditional and Type Expressions

PostgreSQL COALESCE() Function

Return the first argument that isn't NULL. The standard way to replace NULL with a default.

PostgreSQL 18

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

COALESCE(value, ...)

Parameters

ParameterDescription
value Two or more values of compatible types.

Return value

The first non-NULL value, or NULL.

Examples

A default
SELECT name, coalesce(email, 'no email') AS email
FROM customers
WHERE customer_id IN (4, 5);

Output

       name        |       email
-------------------+-------------------
 Linus Torvalds    | [email protected]
 Margaret Hamilton | no email
(2 rows)

Run this on your computer — see “Get Started” for setup.

Several fallbacks
SELECT name, coalesce(city, country) AS place
FROM customers
WHERE customer_id IN (6, 7);

Output

      name       | place
-----------------+--------
 Tim Berners-Lee | Geneva
 Barbara Liskov  | USA
(2 rows)

Run this on your computer — see “Get Started” for setup.

All arguments must share a type: coalesce(price, 'n/a') fails for a numeric price. Cast first: coalesce(price::text, 'n/a').

In other languages

  • NULLIF() · Return NULL if two values are equal, otherwise the first one. Commonly used to avoid division by zero.
  • CASE · Choose a value from conditions: CASE WHEN cond THEN result … ELSE default END, usable anywhere an expression is.
  • GREATEST() · Return the largest of a list of values, ignoring NULLs. least() returns the smallest.
  • SQL NULL Values · tutorial

Was this page helpful?