coalesce()

Overview

Tables can hold null and non-null values. Yet, often we prefer to overlook those null values and this is where COALESCE() steps in. It helps when we want to ignore null values while processing the data, by returning the first argument that is not null, while the remaining arguments from the first non-null argument are not evaluated.

If all arguments are null, the COALESCE function will return null.

Syntax

The syntax for the COALESCE() function is as follows:

COALESCE (argument_1, argument_2, …);

Key points from the syntax:

• COALESCE() requires a minimum of two inputs.
• It can take an unlimited number of arguments.
• Evaluation occurs sequentially from left to right, stopping at the first non-null value.

Examples

Here are some examples to illustrate the application of COALESCE():

Case #1: Returning the First Non-Null Value

In this example, we have a set of values. By using the COALESCE() function, we’re going to get the first non-null value from this set.

SELECT COALESCE(9, 3, 8, 7, 1);

The result will be 9, the first value without null among the provided options.

 coalesce 
----------
        9

Case #2: Handling NULL Value as the Last Argument

Let’s include NULL as the final argument and check the query output.

Select COALESCE(3,4,5,9,10,NULL);

The function output is 3 because it returns the first non-null value.

 coalesce 
----------
        3

Case #3: Handling NULL Value as the First Argument

Consider NULL as the first argument in the following example.

Select COALESCE(NULL,1,5,7,9,2);

The output is 1, as it is the first non-null value of the argument.

 coalesce 
----------
        1

Case #4: Handling Multiple NULL Values

In the following query, NULL appears in the first, second, fourth, and last positions.

Select COALESCE(NULL, NULL ,3, NULL, 7,9,4,5, NULL);

The COALESCE() function ignores the first two NULLs and returns the first non-null value, 3. It does not process the subsequent NULL values.

 coalesce 
----------
        3

Case #5: Handling All NULL Values

Assume that the given values are entirely composed of nulls.

Select COALESCE(NULL, NULL ,NULL, NULL);

In this case, the COALESCE() function returns an empty value (null).

 coalesce 
----------
 

Case #6: COALESCE() with Table Data

Imagine we have the employee_absent table, which comprises a mix of NULL and non-null values:

CREATE TABLE employee_absent (
    emp_name TEXT,
    emp_dept TEXT,
    absent TEXT
);

INSERT INTO employee_absent (emp_name, emp_dept, absent)
VALUES
    ('Alice', 'Finance', 'absent'),
    ('Bob', 'Operations', 'absent'),
    ('Carol', 'Finance', 'absent'),
    ('David', 'HR', NULL),
    ('Emily', 'HR', NULL);

Use the SELECT statement to display all the records:

SELECT * FROM employee_absent;

 emp_name |  emp_dept  | absent 
----------+------------+--------
 Alice    | Finance    | absent
 Bob      | Operations | absent
 Carol    | Finance    | absent
 David    | HR         | 
 Emily    | HR         | 

The query below uses the COALESCE() function on the absent column. It retrieves names and absences (with out of office for NULL values) for each employee.

SELECT emp_name, COALESCE(absent, 'out of office') AS DisplayAbsent FROM employee_absent;

 emp_name | displayabsent 
----------+---------------
 Alice    | absent
 Bob      | absent
 Carol    | absent
 David    | out of office
 Emily    | out of office

Case #7: Error Output in COALESCE()

When specifying arguments with different datatypes, they should be convertible.

Select Coalesce ('x',NULL,1);

If the datatypes cannot be converted, the COALESCE() function will generate an error, as shown below.

ERROR:  invalid input syntax for type integer: "x"