PostgreSQL: NULLIF function:

June 17, 2019July 17, 2019 by HARHSIT JAIN, posted in PostgreSQL, SQL

The syntax of the NULLIF function:

NULLIF(argument_1,argument_2);

The NULLIF function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1.

SELECT
   NULLIF (1, 1); -- return NULL
 
SELECT
   NULLIF (1, 0); -- return 1
 
SELECT
   NULLIF ('A', 'B'); -- return A

Use NULLIF to prevent division-by-zero error:

CREATE TABLE test_nullif (
    numerator int, denominator int)
    
INSERT INTO test_nullif(numerator, denominator) values (10, 0)

select numerator / denominator as result from test_nullif -- ERROR:  division by zero

select numerator / NULLIF(denominator,0) as result from test_nullif
Output: 
result
null

Note: If we want to convert 0 to null, then use NULLIF, while if we want to convert null to 0, then use COALESCE.