What is the NULLIF function in PostgreSQL?

Overview

The NULLIF function in PostgreSQL is an in-built conditional function that takes two arguments and returns null if they equal one another. Otherwise, it returns only the first argument.

The function returns null if either of the arguments is null.

Syntax

NULLIF(arg_1, arg_2);

The function returns null if arg_1 is equal to arg_2, and returns arg_1 otherwise.

Example 1

SQL Output
select nullif(1, 1); null
select nullif(0, 1); 0
select nullif('educative', 'edpresso') educative 
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 Name, COALESCE(NULLIF(Price, 1000), 0) as new_price from Product;

Explanation

  • Lines 1–3: We define the Product table with Name and Price as attributes.
  • Lines 6–11: We insert several records into the Product table. Here, we insert a record where Price is null.
  • Line 14: We check whether the price of a product is equal to 1000. If the price is 1000, the NULLIF function returns null. We choose 0 instead of null using the COALESCE function.

Free Resources

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