Postgres NULLIF Function

In PostgreSQL, the NULLIF() function is a conditional function that allows us to compare two expressions and return NULL if they are equal. Otherwise, the function returns the first expression.

In this tutorial, we will learn how to work with the NULLIF function in PostgreSQL. We will discuss how the function works and cover some basic examples on how to use this function.

PostgreSQL NULLIF Function

The following shows the syntax of the PostgreSQL nullif() function:

NULLIF(expression1, expression2);

The following explains the parameters and the functionality of the function:

Expression1 and expression2 determine the expressions that are evaluated against the function.

If expression1 and expression2 are equal, the function returns NULL. Otherwise, if expression1 and expression2 are not equal, the function returns the expression1.

The main purpose of the NULLIF() function is to handle the situations where you want to replace a specific value with a NULL based on a condition.

Example 1: Basic Functionality of NULLIF()

Consider the following examples that demonstrate the basic functionality of the nullif() function in PostgreSQL:

SELECT NULLIF(10, 10); -- Returns NULL

SELECT NULLIF(10, 20); -- Returns 10

SELECT NULLIF('apple', 'banana'); -- Returns 'apple'

SELECT NULLIF('apple', 'apple'); -- Returns NULL

Example 2: NULLIF() Function with a Database Table

We can also use the nullif() function with a database table. For example, suppose we have a table called “customers” with the customer_id and email columns. If we want to retrieve the customer ID equal to a given value and replace the customer ID with NULL, we can use the NULLIF() function as follows:

SELECT NULLIF(customer_id, 1) AS customer_id

FROM customer

WHERE email = '[email protected]';

In the previous query, we use NULLIF(customer_id, 1) to compare the customer_id column with a given integer. If they are equal, the function returns NULL which effectively replaces the customer ID with NULL.

This should replace the customer_id with NULL as follows:

Example 3: Prevent Division by Zero

We can also use the nullif() function to prevent division by comparing the divisor with zero and replacing it with a NULL if it is true.

An example is as follows:

SELECT NULLIF(10/0, 0) AS result;

This should return an error when you attempt to divide by zero.

ERROR: division by zero SQL state:

22012

Conclusion

We learned how we can use the NULLIF() function to compare two expressions and return NULL if they are equal. Otherwise, return the first expression.