In this post, we are going to understand what the COALESCE function is and how to use it in PostgreSQL.
What is a COALESCE Function?
COALESCE is a system in-built function that can be considered one of the conditional expressions available in PostgreSQL. NULLIF, GREATEST, LEAST, and COALESCE are the types of conditional expressions in PostgreSQL.
The COALESCE function returns the first non-NULL expression in the specified list. If all the arguments are NULL then it will return NULL as its output.
The supported syntax is:
COALESCE(value_1,value_2,value_3,........value_n)
The parameters or arguments are:
value_1,value_2,value_3,........value_n
Caveats
A few points about the COALESCE function:
- The parameters are tested for non-NULL values.
- The parameters should all be the same datatype. Otherwise it will result in an error.
- The COALESCE function accepts an unlimited number of arguments.
- It takes the arguments from left to right.
- Coalesce function provide similar compatibility like NVL and IFNULL which are available in other DBMS.
- CASE and COALESCE can be used for similar purposes, but the advantage of using COALESCE is that it requires far less typing.
- It will stop evaluating parameters as soon as the first non-NULL value is reached.
Examples
1. Here is how COALESCE works with a non-NULL value for the first parameter:
postgres=# select coalesce (1,null,2);
coalesce
----------
1
(1 row)
The COALESCE function finds the first non-NULL expression at the start, so it will always produce the same result regardless of what the remaining parameters contain.
2. Here is how COALESCE works when a NULL value is the first parameter:
postgres=# select coalesce (null,1,2);
coalesce
----------
1
(1 row)
The COALESCE function found a NULL value in the first parameter, so it continued to the second parameter, which was not NULL, so that parameter's value was returned.
3. Here is how it interprets multiple NULL values:
postgres=# SELECT coalesce(null,null, 1, 2, 3, null, 4);
coalesce
----------
1
(1 row)
There are multiple NULLs in this expression, but because the first non-NULL value was reached on the third parameter, all remaining values were disregarded.
4. If the data types of the parameters don't match, this will produce an error:
postgres=# SELECT coalesce(1,now(),null);
ERROR: COALESCE types integer and timestamp with time zone cannot be matched
The first parameter is an integer, and the now() function returns a timestamp with time zone. Because these parameters are not of the same type, the signature of the function is invalid, and so produces and error.
5. You can use other functions as parameters inside the COALESCE function:
postgres=# SELECT coalesce(nvl(null,199),least(8,null),avg(9.9));
coalesce
----------
199
(1 row)
Preventing NULL values with COALESCE
A helpful application of the COALESCE function is to prevent NULL values from appearing in query results.
Example
1. Create a table “sales” using the CREATE TABLE command:
postgres=# CREATE TABLE sales
(serial_id int primary key,
product_name varchar(50),
amount numeric,
discount numeric);
CREATE TABLE
2. Insert data into table “sales”:
postgres=# INSERT INTO sales
VALUES
(121,'paste',1000,10),
(144,'brush',1500,20),
(166,'soap',800,0),
(178,'coffee',500,NULL);
INSERT 0 4
3. If we try to perform subtract a value in the "discount" column from a value in the "amount" column, the last row will return a NULL value. This is because any arithmetic performed on a NULL value will result in NULL.
postgres=# SELECT amount - discount as "final bill" FROM sales;
final bill
------------
990
1480
800
<=Null
(4 rows)
4. To overcome this result, we can use COALESCE to make NULL values return 0 in this particular case.
postgres=# SELECT amount - coalesce(discount,0) AS "final bill" FROM sales;
final bill
------------
990
1480
800
500 <== Correct value generated if discount is 0
(4 rows)
Hope it helps!