What is COALESCE in PostgreSQL?

Overview

COALESCE in PostgreSQL is an in-built function that returns the first non-null value/expression in the given list of values/expressions.

Parameters

• The function takes unlimited arguments.
• The COALESCE function expects the arguments to be of the same datatype. Otherwise, it throws an error.

Return value

This function returns the first non-null argument.

Syntax

COALESCE (arg_1, arg_2, arg_3, …);

The evaluation of the function is from left to right. In the syntax above, the null check will be performed for arg_1, then for arg_2.

Code example 1

Let’s look at the code below:

Code explanation

In the above example, we pass different arguments to the COALESCE() function. Some of the observations are:

• The output is educative. It is the first non-null argument.
• Once the function reaches the second argument, that is, educative, further arguments are ignored.

Code example 2

Let’s look at another example:

Code explanation

• Lines 1 to 3: We define the Product table with Name and Price as attributes.
• Lines 6 to 11: We insert several records into the Product table. Here, we insert a record where Price is NULL.
• Line 13: We calculate the discount in the Price column. Here, the COALESCE() function is used with arguments Price and 0. So 0 is returned when any Price value is NULL.