In PostgreSQL, the COALESCE() operator returns the first non-null value from its list of arguments. If all arguments are null, it returns null. 

Syntax

The syntax goes like this:

COALESCE(value [, ...])

So, at least one argument is required, but multiple arguments can be (and usually are) provided.

Example

Here’s a simple example to demonstrate:

SELECT COALESCE(null, 'Super', 'Yacht');

Result:

Super

In this case, Super was the first non-null value, and so COALESCE() returned that value.

Yacht was also a non-null value, but it came after Super and so it wasn’t returned.

When All Values are Null

If all values are null, COALESCE() returns null:

\pset null '(null)'
SELECT COALESCE( null, null );

Result:

(null)

Here, the first line sets a value to be output for null values. This has got nothing to do with the COALESCE() operator, I just put it there to help with the example. By default, the psql terminal outputs the empty string whenever a null value is returned. In this case, I set it to output (null) whenever a null value is returned.

Expressions

COALESCE() returns the current value of the first expression that initially doesn’t evaluate to null. Therefore, if we pass an expression like this:

SELECT COALESCE( null, 3 * 5 );

We get this:

15

Database Example

Suppose we run the following query:

\pset null '(null)'
SELECT 
  name,
  salary
FROM employee;

And we get the following result:

Null display is "(null)".
 name  | salary 
-------+--------
 Elise | 100000
 Rohit |  50000
 Homer | (null)

We can see that the last row has a null value in the DOB column. This is represented as (null) because I used \pset to set a value to be output whenever a null value is returned.

If we wanted to replace (null) with another value, we could change the query as follows:

SELECT
  name,
  COALESCE(salary, 0) AS salary
FROM employee;

Result:

 name  | salary 
-------+--------
 Elise | 100000
 Rohit |  50000
 Homer |      0

In this case we replaced all null values with the integer 0.

Invalid Argument Count

Using COALESCE() without any arguments results in an error:

SELECT COALESCE();

Result:

ERROR:  syntax error at or near ")"
LINE 1: SELECT COALESCE();
                        ^