COALESCE

21min
Overview
A table can hold empty (null) and filled (non-null) values. Yet, often, we prefer to overlook those empty values. This is where COALESCE() steps in.
COALESCE() function helps us when we want to ignore null values while processing data. It returns 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:
Pgsql
|
COALESCE (argument_1, argument_2, …);
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.
Pgsql
|
SELECT COALESCE(9, 3, 8, 7, 1);
SELECT COALESCE(9, 3, 8, 7, 1);
﻿
The result will be 9, the first value without null among the provided options.
Pgsql
|
 coalesce 
----------
        9
 coalesce 
----------
        9
﻿
Case #2: Handling NULL Value as the Last Argument
Let’s include NULL as the final argument and check the query output.
Pgsql
|
Select COALESCE(3,4,5,9,10,NULL);
Select COALESCE(3,4,5,9,10,NULL);
﻿
 The function output is 3 because it returns the first non-null value.
Pgsql
|
 coalesce 
----------
        3
 coalesce 
----------
        3
﻿
Case #3: Handling NULL Value as the First Argument
Consider NULL as the first argument in the following example.
Pgsql
|
Select COALESCE(NULL,1,5,7,9,2);
Select COALESCE(NULL,1,5,7,9,2);
﻿
The output is 1, as it is the first non-null value of the argument.
Pgsql
|
 coalesce 
----------
        1
 coalesce 
----------
        1
﻿
Case #4: Handling Multiple NULL Values
In the following query, NULL appears in the first, second, fourth, and last positions.
Pgsql
|
Select COALESCE(NULL, NULL ,3, NULL, 7,9,4,5, NULL);
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.
Pgsql
|
 coalesce 
----------
        3
 coalesce 
----------
        3
﻿
Case #5: Handling All NULL Values
Assume that the given values are entirely composed of nulls.
Pgsql
|
Select COALESCE(NULL, NULL ,NULL, NULL);
Select COALESCE(NULL, NULL ,NULL, NULL);
﻿
In this case, the COALESCE() function returns an empty value (null).
Pgsql
|
 coalesce 
----------
 
 coalesce 
----------
 
﻿
Case #6: COALESCE() with Table Data
Imagine we have the employee_absent table, which comprises a mix of NULL and non-null values:
Pgsql
|
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);
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:
Pgsql
|
SELECT * FROM employee_absent;
SELECT * FROM employee_absent;
﻿
Pgsql
|
 emp_name |  emp_dept  | absent 
----------+------------+--------
 Alice    | Finance    | absent
 Bob      | Operations | absent
 Carol    | Finance    | absent
 David    | HR         | 
 Emily    | HR         | 
 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.
Pgsql
|
SELECT emp_name, COALESCE(absent, 'out of office') AS DisplayAbsent FROM employee_absent;
SELECT emp_name, COALESCE(absent, 'out of office') AS DisplayAbsent FROM employee_absent;
﻿
Pgsql
|
 emp_name | displayabsent 
----------+---------------
 Alice    | absent
 Bob      | absent
 Carol    | absent
 David    | out of office
 Emily    | out of office
 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. 
Pgsql
|
Select Coalesce ('x',NULL,1);
Select Coalesce ('x',NULL,1);
﻿
If the datatypes cannot be converted, the COALESCE() function will generate an error, as shown below.
Pgsql
|
ERROR:  invalid input syntax for type integer: "x"
ERROR:  invalid input syntax for type integer: "x"
﻿
﻿