What is COALESCE in PostgreSQL?

Overview

COALESCE in PostgreSQL is an in-built function that returns the first non-null value/expression in the given list of values/expressions.

Parameters

  • The function takes unlimited arguments.
  • The COALESCE function expects the arguments to be of the same datatype. Otherwise, it throws an error.

Return value

This function returns the first non-null argument.

Syntax

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.

Code example 1

Let’s look at the code below:

SELECT  COALESCE(NULL, 'educative', 'edpresso', NULL, 'hello') as coalesce_output;

Code explanation

In the above example, we pass different arguments to the COALESCE() function. Some of the observations are:

  • The output is educative. It is the first non-null argument.
  • Once the function reaches the second argument, that is, educative, further arguments are ignored.

Code example 2

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;

Code explanation

  • Lines 1 to 3: We define the Product table with Name and Price as attributes.
  • Lines 6 to 11: We insert several records into the Product table. Here, we insert a record where Price is NULL.
  • Line 13: We calculate the discount in the Price column. Here, the COALESCE() function is used with arguments Price and 0. So 0 is returned when any Price value is NULL.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)