How to Use COALESCE in PostgreSQL: Syntax and Examples

In PostgreSQL, the COALESCE function returns the first non-NULL value from a list of expressions. It is useful to replace NULLs with a default value or to combine multiple columns safely.

Syntax

The COALESCE function takes two or more arguments and returns the first argument that is not NULL.

• expression1, expression2, ...: Values or columns to check in order.

If all arguments are NULL, it returns NULL.

sql

COALESCE(expression1, expression2, ..., expressionN)

Example

This example shows how COALESCE returns the first non-NULL value from multiple columns in a table.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  first_name TEXT,
  middle_name TEXT,
  last_name TEXT
);

INSERT INTO employees (first_name, middle_name, last_name) VALUES
('John', NULL, 'Doe'),
(NULL, 'Michael', 'Smith'),
(NULL, NULL, NULL);

SELECT id,
       COALESCE(first_name, middle_name, last_name, 'No Name') AS display_name
FROM employees
ORDER BY id;

Output

 id | display_name 
----+--------------
  1 | John         
  2 | Michael      
  3 | No Name      
(3 rows)

Common Pitfalls

Common mistakes when using COALESCE include:

• Using it with incompatible data types, which causes errors.
• Expecting it to replace all NULLs in a column without specifying all fallback values.
• Confusing COALESCE with ISNULL (which is not standard in PostgreSQL).

sql

/* Wrong: mixing text and integer without casting */
SELECT COALESCE(NULL, 5, 'text');

/* Right: cast all to text */
SELECT COALESCE(NULL::text, '5', 'text');

Quick Reference

Usage	Description
COALESCE(expr1, expr2, ...)	Returns first non-NULL expression
Returns NULL if all expressions are NULL	Default behavior
Use with compatible data types	Avoid type errors
Can replace NULL with default value	Common use case
Supports any number of arguments	Flexible usage

Key Takeaways

COALESCE returns the first non-NULL value from its arguments.

All arguments should be of compatible data types to avoid errors.

Use COALESCE to provide default values when dealing with NULLs.

It can take two or more arguments and returns NULL only if all are NULL.

COALESCE is standard SQL and preferred over non-standard functions like ISNULL.