PostgreSQL NULLIF Function

Summary: in this tutorial, you’ll learn how to use the PostgreSQL NULLIF function to compare two expressions and return NULL if they are equal.

Getting started with the PostgreSQL NULLIF function #

In PostgreSQL, the NULLIF function compares two expressions and returns NULL if they are equal. Otherwise, it returns the result of the first expression.

Here’s the syntax of the NULLIF function:

NULLIF(expression1, expression2)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If expression1 equals expression2, the function returns NULL. Otherwise, it returns expression1.

Note that the NULLIF function compares the results of two expressions using the equal operator (=). This means that the results of both expressions must be comparable. In other words, they must have a compatible data type for comparison.

Basic PostgreSQL NULLIF function examples #

The following example returns NULL because both values are equal:

SELECT NULLIF(0, 0) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output

 result
--------
   NULLCode language: PHP (php)

The following example returns 1 because 1 is not equal to 0:

SELECT NULLIF(1, 0) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output

 result
--------
      1

The following example always returns NULL because NULL is not equal to NULL.

SELECT NULLIF(NULL, NULL) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 NULLCode language: PHP (php)

The following example returns when comparing a string ‘1’ with the number 1:

SELECT NULLIF('1' , 1) result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
   NULLCode language: PHP (php)

It returns the same result as you compare the ‘1’ and 1 using the equal operator:

SELECT '1' = 1 result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output

 result
--------
 t

The ‘1’ is equal 1, so the ‘1’= 1 returns true. The NULLIF('1', 1) returns NULL.

Using PostgreSQL NULLIF function to replace a value with NULL #

The NULLIF function can help replace a value with NULL, especially for data cleansing.

In the application, users may enter an empty string ('') or N/A into a column into a nullable column.

To fix this issue, you need to validate the data at the application layer and clean up data at the database level; you can use the NULLIF function to replace these invalid values with NULL.

Suppose you have the following contacts table that store contact id, name, phone and email:

CREATE TABLE contacts(
    contact_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(25),
    email VARCHAR(255)
);
INSERT INTO contacts (name, phone, email) 
VALUES
('Alice Johnson', 'N/A', ''),
('Bob Smith', '123-456-789', '[email protected]'),
('Carol White', '123-456-788', '[email protected]'),
('David Brown', '123-456-787', '[email protected]' ),
('Eve Black', '123-456-786', '')
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

We can use an UPDATE statement and NULLIF function to update the invalid values in the phone and email columns as follows:

UPDATE contacts
SET
  phone = NULLIF(phone, 'N/A'),
  email = NULLIF(email, '') 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 contact_id |     name      |    phone    |           email
------------+---------------+-------------+----------------------------
          1 | Alice Johnson | NULL        | NULL
          2 | Bob Smith     | 123-456-789 | [email protected]
          3 | Carol White   | 123-456-788 | [email protected]
          4 | David Brown   | 123-456-787 | [email protected]
          5 | Eve Black     | 123-456-786 | NULLCode language: PHP (php)

Preventing the division by zero error #

The following statement retrieves the safety stock from the products table joins it with inventory quantity from the inventories table, and calculates the stock cover ratio:

WITH
  stocks AS (
    SELECT
      product_name,
      safety_stock,
      SUM(quantity) inventory_qty
    FROM
      products
      INNER JOIN inventories USING (product_id)
    GROUP BY
      product_name,
      safety_stock
  )
SELECT
  product_name,
  inventory_qty / safety_stock stock_coverage
FROM
  stocks
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

division by zero

The output shows the division by zero error.

The reason is that some products have safety stock 0. To prevent the division by zero error, you can use the NULLIF statement to return NULL if the safety stock is zero:

WITH
  stocks AS (
    SELECT
      product_name,
      NULLIF(safety_stock, 0) safety_stock,
      SUM(quantity) inventory_qty
    FROM
      products
      INNER JOIN inventories USING (product_id)
    GROUP BY
      product_name,
      safety_stock
  )
SELECT
  product_name,
  inventory_qty,
  safety_stock,
  inventory_qty / safety_stock stock_coverage
FROM
  stocks
ORDER BY
  product_name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Partial Output:

        product_name        | inventory_qty | safety_stock | stock_coverage
----------------------------+---------------+--------------+----------------
 Apple AirPods Pro 3        |           180 |           45 |              4
 Apple iMac 24"             |           320 |         NULL |           NULL
 Apple iPad Pro 12.9        |           170 |           15 |             11
 Apple iPhone 15            |           150 |           20 |              7
 Apple iPhone 15 Pro Max    |           140 |           50 |              2
 Apple Watch Series 9       |           200 |           20 |             10
 Bose SoundLink Max         |           270 |         NULL |           NULL
...

In this example, if the safety stock is 0, the NULLIF statement returns NULL; otherwise, it returns the safety stock. Dividing an inventory by NULL will result in NULL instead of an error.

Summary #

• Use the PostgreSQL NULLIF function to return NULL if two arguments are equal or the first argument otherwise.
• Use the NULLIF function to replace a value with NULL or prevent division by zero by replacing 0 with NULL.

Quiz #