PostgreSQL COALESCE Function: Get First Non-NULL Value

In PostgreSQL, the COALESCE() function is used to get the first non-null value among the specified arguments. It returns the first of its arguments that is not null. If all arguments are NULL, it will return a NULL value.

It is often used to substitute a default value for null values

The COALESCE function is the same as the SQL standard NVL() function in Oracle and IFNULL() function in MySQL.

The COALESCE evaluates the list of arguments provided from left to right until it finds out first NOT NULL argument and returns it. The remaining arguments after the first NOT NULL argument will not be evaluated.

Example: COALESCE() COALESCE()

SELECT COALESCE(null, 8, 9, null, 10 );

It can be used to replace null values in a column with other values for display purposes. Note that it does not update the value.

Example: COALESCE()

SELECT first_name, COALESCE(email,'None') from Employee;

All the arguments specified in COALESCE function must be convertible to a common datatype otherwise it will raise an error.

Example: COALESCE()

SELECT COALESCE(NULL, 6, 'A');

In the above query second argument is the number and 3rd argument is the character 'A', it will raise an error as 2nd and 3rd argument cannot be converted to a common data type.