[PostgreSQL] COALESCE

WHAT IS COALESCE()?

In simple terms, COALESCE() helps you choose the first non-null alue from a list.

BASIC SYNTAX

COALESCE(value1, value2, value3, ...)

It checks each value in order and returns the first one that is not NULL.

SIMPLE EXAMPLES

-- example 1
SELECT COALESCE(NULL, NULL, 'backup') -- returns 'backup'

-- example 2
SELECT COALESCE(name, 'No name found') FROM users;
-- returns name if there is name, or 'No name found'.

WHY IS COALESCE() USEFUL?

In real-world datasets, some columns are incomplete.

With COALESCE(), you can fill in the blanks, or choose a preferred source of truth.

REAL BI EXAMPLES: COMBINING CUSTOMER INFO FROM MULTIPLE DATASETS

In my case, I had three sources of customer names:

• client_info: having client_id, client_name(full name), but some of list are missed.

• visit_history: having client_id, first name, last name

• invoice: having client_id, client_name(full name)

So I needed to create a unified list of all client IDs and assign the best available name.

WITH all_clients AS (
  SELECT client_id FROM client_info
  UNION
  SELECT client_id FROM visit_history
  UNION
  SELECT client_id FROM invoice
)

SELECT
  a.client_id,
  COALESCE(ci.client_name, CONCAT(vh.firstname, ' ', vh.lastname), i.client_name) AS client_name
FROM all_clients a
LEFT JOIN client_info ci ON a.client_id = ci.client_id
LEFT JOIN visit_history vh ON a.client_id = vh.client_id
LEFT JOIN invoice i ON a.client_id = i.client_id;

COALESCE() logic gave me the first available client name in priority order

1. From client_info

2. If missing, combine firstname + lastname from visit_history

3. If still missing, fallback to name in invoice

MORE EXAMPLES IN ACTION

-- example 1: Choose the most complete email
SELECT COALESCE(primary_email, secondary_email, 'no-email@unknown.com') AS best_email
FROM customer_emails;

-- example 2: Fill missing state data
SELECT COALESCE(state, 'CA') AS state_filled
FROM client_address_california

COALESCE() vs. IFNULL() vs. CASE

SUMMARY

Use COALESCE() when

• You have multiple possible values for a field

• You want to avoid NULLs in your output

• You’re combining datasets with missing pieces