PostgreSQL COALESCE Function

Summary: in this tutorial, you will learn how to use the PostgreSQL COALESCE() function to handle NULL in the database.

Getting Started with PostgreSQL COALESCE() function #

In PostgreSQL, NULL means an unknown value. NULL indicates that the data is unknown or missing when recording.

The PostgreSQL COALESCE() function takes a list of arguments and returns the first non-NULL one. The following shows the syntax of the COALESCE() function:

COALESCE(value1, value2, ...)

The COALESCE function accepts a variable number of arguments (value1, value2, …). It evaluates arguments from left to right and returns the first one that is not NULL.

The COALESCE function returns NULL if all arguments are NULL.

PostgreSQL COALESCE() function examples #

We’ll use the profiles table from the inventory database for the demonstration purposes.

The following example retrieves the first name and work phone number from user profiles:

SELECT
  first_name,
  work_phone
FROM
  profiles
ORDER BY
  first_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 first_name |  work_phone
------------+--------------
 Alice      | NULL
 Bob        | NULL
 Charlie    | NULL
 Jane       | 408-456-7891
 John       | 408-456-7890Code language: PHP (php)

The output indicates that Alice, Bob, and Charlie have work phone numbers as NULL.

To make the list more business-friendly, you can use the COALESCE function to replace NULL with N/A (Not Available).

SELECT
  first_name,
  COALESCE(work_phone, 'N/A') work_phone
FROM
  profiles
ORDER BY
  first_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 first_name |  work_phone
------------+--------------
 Alice      | N/A
 Bob        | N/A
 Charlie    | N/A
 Jane       | 408-456-7891
 John       | 408-456-7890

In this example, the COALESCE returns the work phone if it is not NULL or N/A if it is NULL.

Suppose you print out a contact list for all users, including first name and phone number. You can use the home phone number if the work phone number is unavailable.

To do that, you can use the COALESCE function as follows:

SELECT
  first_name,
  COALESCE(work_phone, home_phone) phone
FROM
  profiles
ORDER BY
  first_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 first_name |    phone
------------+--------------
 Alice      | 408-111-4444
 Bob        | 408-111-5555
 Charlie    | NULL
 Jane       | 408-456-7891
 John       | 408-456-7890Code language: PHP (php)

In this statement, the COALESCE will return the work phone number if it is not null or the home phone number.

The output indicates that Charlie does not have work and home phone numbers.

You can replace that NULL with N/A in the COALESCE function as follows:

SELECT
  first_name,
  COALESCE(work_phone, home_phone, 'N/A') phone
FROM
  profiles
ORDER BY
  first_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 first_name |    phone
------------+--------------
 Alice      | 408-111-4444
 Bob        | 408-111-5555
 Charlie    | N/A
 Jane       | 408-456-7891
 John       | 408-456-7890

Summary #

• Use the PostgreSQL COALESCE function to retrieve the first non-null argument.

Quiz #