COALESCE for NULL handling in PostgreSQL

Introduction

COALESCE helps you pick the first value that is not empty or missing from a list. It makes sure you get a real value instead of nothing.

When you want to show a default value if some data is missing.

When combining columns that might have empty spots and you want the first filled one.

When you want to avoid errors caused by missing values in calculations.

When you want to clean up reports by replacing empty fields with meaningful text.

Syntax

PostgreSQL

COALESCE(value1, value2, ..., valueN)

It checks each value from left to right and returns the first one that is not NULL.

If all values are NULL, it returns NULL.

Examples

Returns 'apple' because it is the first non-NULL value.

PostgreSQL

SELECT COALESCE(NULL, 'apple', 'banana');

Returns 10 as it is the first non-NULL number.

PostgreSQL

SELECT COALESCE(NULL, NULL, 10, 20);

Returns NULL because all values are NULL.

PostgreSQL

SELECT COALESCE(NULL, NULL, NULL);

Sample Program

This creates a table with some missing names and colors. The query shows the first available value or 'Unknown' if both are missing.

PostgreSQL

CREATE TABLE fruits (
  id SERIAL PRIMARY KEY,
  name TEXT,
  favorite_color TEXT
);

INSERT INTO fruits (name, favorite_color) VALUES
  ('Apple', NULL),
  (NULL, 'Red'),
  (NULL, NULL);

SELECT id, COALESCE(name, favorite_color, 'Unknown') AS display_name FROM fruits ORDER BY id;

Important Notes

COALESCE is very useful to avoid showing empty or NULL values in your results.

You can use COALESCE with any data type as long as all values are compatible.

It is a simple way to provide fallback values in your queries.

Summary

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

Use it to replace missing data with defaults or alternatives.

It helps keep your query results clean and meaningful.