Effective way to use COALESCE in PostgreSQL

The COALESCE in PostgreSQL returns the first non-null argument value. PostgreSQL provides various functions to handle NULL-value scenarios. The coalesce function in PostgreSQL is one of the important functions to handle such scenarios.

Coalesce can accept multiple arguments. It internally selects the argument starting from the left argument, which is not null. After finding the not-null argument, it returns that argument as a return value. For example, if we provide three arguments to coalesce and the first two are NULL and the third one has some value, then coalesce will return the third argument value.

COALESCE in PostgreSQL will work similarly to the ISNULL function in MS SQL or Microsoft SQL Server.

Note: If all the COALESCE arguments are NULL, then it will return NULL value.

COALESCE Syntax

Below is the syntax of the COALESCE function in PostgreSQL.

COALESCE(<arg1>, [<arg2>], [<arg3>] [,...])

We can specify any number of arguments to the COALESCE function. Only the first argument is required. Rest arguments are optional.

COALESCE in PostgreSQL Example

Let’s try to understand the behavior of COALESCE by using a simple example. Suppose we have provided two values in the COALESCE function: a and b.

SELECT COALESCE('a', 'b')

In the above example, we have provided two arguments to the COALESCE function. As both arguments are not null, the COALESCE function returns the first ‘a’ value.

In the second example, we have assigned a NULL value to the first argument without changing the second argument.

In the result, we got the “b” value because the first argument is null and the second argument is not.

In a similar way, we assign three arguments to the COALESCE function; the first two arguments are NULL, and the third one is not.

The COALESCE function returns the ‘c’ value, as the first two arguments are NULL.

COALESCE in WHERE clause

We can use the COALESCE in the where clause in special scenarios. Suppose we want to find those rows where one or multiple columns are null. Then we can use the COALESCE function. Below is an example:

Let’s take an example. We have an employee table with four columns: employee_id, name, salary, and department_id.

Now, if we want to find those rows where salary and department_id are NULL, then we can use COALESCE.

SELECT * from employee
WHERE COALESCE(salary, department_id, -1) = -1

In the above query, we are passing salary, department_id, and -1 arguments to the COALESCE function. When both salary and department_id are null, the COALESCE function returns the -1 value, which matches the where condition.

COALESCE with Empty String in PostgreSQL

We can also use COALESCE to check for empty strings in a column of the PostgreSQL table. To do that, we need to use the NULLIF function with the COALESCE function.

NULLIF function returns a null value if both its arguments are equal.

Below is an example:

SELECT COALESCE (NULLIF(name, ''), '(none)')
FROM employee

If name value is an empty string, then NULLIF returns null value, and COALESCE will return the second argument (none).

COALESCE with Integer

We can also use the COALESCE function with integer columns, which has the common requirement of converting NULL to a 0 value in PostgreSQL. To do that, we need to pass 0 in the second argument of the COALESCE function, as shown below:

SELECT name, COALESCE(salary, 0)
FROM employee

In the above query, Bob Johnson salary data is not available, so the query will return the 0 value instead of showing NULL.

COALESCE with Dates

COALESCE will also be used with date data. We have two scenarios where we show the default value with dates when date data is not available.

1. COALESCE with custom date
2. COALESCE with system current date

COALESCE with Custom date default value

We can use COALESCE to show a custom date when the date column is null. Below is the query:

SELECT name, COALESCE(dob, '1900-01-01'::date)
FROM employee;

COALESCE with System Current Date

To show the system’s current date in a query, use the CURRENT_DATE field with COALESCE. Below is an example:

SELECT name, COALESCE(dob, CURRENT_DATE)
FROM employee;

PostgreSQL Array

PostgreSQL also supports array data types. In the array data type, we can store different values of the same type in a single column.

COALESCE with NULL Array

For the illustration, I have created an example table in which we have a null value in one of the rows of the array column.

To return the blank array instead of a null value in PostgreSQL, we can use COALESCE. Below is an example.

SELECT id, COALESCE(numbers, ARRAY[]::INTEGER[]) 
FROM example_table;

COALESCE with Empty Array

If we have an empty array in our table, then we can use COALESCE to return some default value from the query. We can use NULLIF to check an empty array. Below is an example.

SELECT id, COALESCE(NULLIF(NUMBERS, ARRAY[]::INTEGER[]), ARRAY[0])
FROM example_table;

In the second argument of COALESCE, I have passed an array with “0” as the default value.

COALESCE with Aggregate Functions

Aggregate functions in PostgreSQL perform calculations on multiple rows and provide you with a single value. Some of the aggregate functions are:

1. SUM()
2. AVG()
3. COUNT()
4. MAX()
5. MIN()

If we have NULL values in some of the rows, then these aggregate functions will skip the null values and give their output without considering null rows. To resolve this, we can use the COALESCE function to replace NULL with some default value.

For example, we have an employee table with 10 rows, and when we count the salary in the 10 rows, of which one row has a NULL value, the count aggregate function will return only 9.

To get the correct output, use the COALESCE with COUNT function.

select COUNT(COALESCE(salary, 0)) from employee
where employee_id < 11;

COALESCE with Multiple Columns

We can use COALESCE with multiple columns in a single query. Below is an example.

SELECT COALESCE(name, '(none)'), COALESCE(salary, 0), COALESCE(department_id, -1)
FROM employee;

To read more about COALESCE, refer to the official documentation of COALESCE in PostgreSQL.

Summary

The COALESCE function in PostgreSQL is important for handling null-value scenarios. It selects the first non-null argument from the list of arguments. The COALESCE functionality is similar to the ISNULL function in other databases.

Read More

• 2 Easy ways to handle the ISNULL function in PostgreSQL
• Mastering SUBSTRING Function in PostgreSQL