Cloud

COALESCE

The COALESCE function returns the first non-NULL argument from a list of arguments. After finding the first non-NULL argument, the function stops evaluating the remaining arguments.

If all arguments are NULL, COALESCE returns NULL.

Syntax

The syntax for this function is:

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:

Return the first non-null value

In this example, a set of values is provided. The COALESCE function returns 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

Handle NULL value as the last argument

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

Handle NULL value as the first argument

Consider NULL as the first argument in this example:

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

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

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

Handle multiple NULL values

In this 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

Handle 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
----------

COALESCE with table data

Consider 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 following query 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

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 generates an error.

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