COALESCE in PostgreSQL is an in-built function that returns the first non-null value/expression in the given list of values/expressions.
COALESCE function expects the arguments to be of the same datatype. Otherwise, it throws an error.This function returns the first non-null argument.
COALESCE (arg_1, arg_2, arg_3, …);
The evaluation of the function is from left to right. In the syntax above, the null check will be performed for arg_1, then for arg_2.
Let’s look at the code below:
SELECT COALESCE(NULL, 'educative', 'edpresso', NULL, 'hello') as coalesce_output;
In the above example, we pass different arguments to the COALESCE() function. Some of the observations are:
educative. It is the first non-null argument.educative, further arguments are ignored.Let’s look at another example:
CREATE TABLE Product (Name varchar(100) NOT NULL,Price int);INSERT INTO Product(Name, Price)VALUES ('mobile', 120),('TV', 200),('Remote', NULL),('Laptop', 1000);select *, COALESCE(Price, 0) * 0.05 as Discount from Product;
Product table with Name and Price as attributes.Product table. Here, we insert a record where Price is NULL.Price column. Here, the COALESCE() function is used with arguments Price and 0. So 0 is returned when any Price value is NULL.