PostgreSQL -COALESCE Statement
The COALESCE function is used to return the first non-null expression from a list of expressions. It’s often used to provide a default value when dealing with nullable columns or expressions. If the first expression in the list is not NULL, the COALESCE function will return that expression; otherwise, it will return the first non-null expression in the list.
The syntax of the COALESCE function is as follows:
COALESCE(expression1, expression2, ..., expressionN)Here’s an example of using the COALESCE function:
SELECT COALESCE(column_name, default_value) AS result FROM table_name;SELECT product_name, COALESCE(discount, 0) AS final_discount FROM products;In this example, if the discount column is NULL, the COALESCE function will return 0 as the final_discount.
The COALESCE function is particularly useful to handle potential NULL values gracefully and provide default values for them.