PostgreSQL COALESCE Function: Complete Guide

In PostgreSQL, the COALESCE function is used to return the first non-null value in a list of parameters. Its usage is as follows:

COALESCE(value1, value2, ...)

Each value in the parameter list can be an expression, column name, constant, or null. The COALESCE function will check each value in order and return the first non-null value. If all values are null, then the function will return null.

Below are examples of the COALESCE function:

SELECT COALESCE(null, 'Hello', 'World'); -- 返回 'Hello'
SELECT COALESCE(null, null, 'World'); -- 返回 'World'
SELECT COALESCE(null, null, null); -- 返回 null
SELECT COALESCE(column1, column2, column3, 'Unknown') FROM table; -- 返回column1、column2、column3中的第一个非null值，如果都为null则返回'Unknown'

The COALESCE function is very useful when handling values that may be null, as it can simplify the code and improve readability.