PostgreSQL COALESCE() Function

The PostgreSQL COALESCE() function returns the first non-null expression in the list. If all expressions evaluate to NULL, then this function will return NULL.

Syntax

COALESCE(expression1, expression2, ... expression_n)

Parameters

Return Value

Returns the first non-null expression from the given list. The function returns any datatype such as a string, numeric, date, etc., but all expressions must be the same datatype.

Example 1:

The example below shows the usage of COALESCE() function.

SELECT COALESCE(NULL, NULL, NULL, 'London', 'Paris');
Result: 'London'

SELECT COALESCE('Mumbai', NULL, NULL, 'London', 'Paris');
Result: 'Mumbai'

SELECT COALESCE(NULL, NULL, NULL, NULL, NULL);
Result: NULL

SELECT COALESCE(1, 2, NULL, NULL, 3);
Result: 1

SELECT COALESCE(NULL, 10, NULL, 20, 30);
Result: 10

Example 2:

Consider a database table called Product with the following records:

If the OrderQuantity is optional and can contain NULL values. The statement mentioned below will give NULL value.

SELECT *, Price * (StockQuantity + OrderQuantity) AS Inventory  
FROM Product;

This will produce the result as shown below:

To avoid such situations, the COALESCE() function can be used to provide an alternative value to the column if it contains NULL value.

SELECT *, Price * (StockQuantity + COALESCE(OrderQuantity, 0)) AS Inventory
FROM Product;

This will produce the result as shown below: