PostgreSQL provides several functions to deal with the NULL values, such as COALESCE() function, NULLIF() function, etc. The NULLIF is one of the most frequently used conditional expressions that deal with the null values.

This post will elaborate on the below-listed concepts of the NULLIF() function:

• What is NULLIF() and How to Use it in PostgreSQL?
• Basic Syntax
• How to Use NULLIF Function in PostgreSQL?

So, let’s get started!

What is NULLIF() and How to Use it in PostgreSQL?

It is a built-in function in Postgres that accepts a couple of arguments and retrieves a NULL value if both arguments are equal or if either of the specified arguments is NULL. It retrieves the first argument if both arguments are not equal and both of the specified arguments are non-null.

Basic Syntax

The below snippet illustrates the usage of the NULLIF() function in PostgreSQL:

NULLIF(argument_1, argument_2);

How to Use NULLIF Function in PostgreSQL?

The NULLIF() function can be better understood by implementing it practically. So, let’s do it!

Example #1: Both Arguments Are Equal

Let’s consider the below snippet to learn how the NULLIF() function deals with the equal arguments:

SELECT NULLIF('Hello', 'Hello');

The output clarifies that the NULLIF function retrieves “NULL” when both arguments are equal.

Example #2: Both Arguments are Different

Let’s specify different values for both the arguments:

SELECT NULLIF('Command', 'Prompt');

The output shows that the NULLIF() function retrieves the value of the first argument when both arguments have different values.

Example #3: How to Use NULLIF() Function on Table’s Data?

Let’s create a new table named std_info with three columns: std_id, std_name, std_hobbies:

CREATE TABLE std_info(
std_id SERIAL PRIMARY KEY,
std_name TEXT NOT NULL,
std_hobbies TEXT
);

The std_info table with three columns has been created successfully. Now we will insert some records into the std_info table using the INSERT INTO command:

INSERT INTO std_info(std_name, std_hobbies)
VALUES('Joe', 'Reading Books'),
('John', ''),
('Ambrose', 'Traveling'),
('Mike', NULL),
('Jones', 'Sports'),
('Seth', NULL');

Output states that six records have been inserted into the std_info table. Let’s run the SELECT command to fetch the newly inserted records of the std_info table:

SELECT * FROM std_info;

The output depicts that the std_hobbies column has some null values. Let’s utilize the NULLIF() function with the collaboration of the COALESCE() function to replace the ‘ ’, or NULL value with the “Watching TV” value in the std_hobbies column:

SELECT std_id, std_name, std_hobbies, 
COALESCE(NULLIF(std_hobbies, ''), 'Watching TV') AS updated_hobbies
FROM std_info;

In the above-given statements, we used the NULLIF function to check whether any student had left the std_hobbies column as null or blank ‘ ’. If yes, then specify a value “Watching TV” in place of that record using the COALESCE() function:

The output authenticates the working of the NULLIF() function.

Conclusion

The NULLIF is one of the most frequently used conditional expressions that deal with the null values. It accepts a couple of arguments and retrieves a NULL value if both arguments are equal or if either of the specified arguments is NULL. It retrieves the first argument if both arguments are not equal and both of the specified arguments are non-null. Through practical examples, this post explained what NULLIF is and how it works in PostgreSQL.