Learn more about NULLIF function in Postgres from our experts. Our PostgreSQL Support team is here to help you with your questions and concerns.

All About NULLIF in Postgres

PostgreSQL tends to offer challenges posed by NULL values in the data.

In other words, these pesky NULLs can complicate queries and calculations.

Fortunately, PostgreSQL offers a powerful tool to handle this issue: the NULLIF() function.

Today, we are going to take a look at NULLIF(), and explore its basic syntax and practical applications.

NULLIF() is a built-in function in PostgreSQL. It takes two arguments and returns a NULL value if they are equal, or if either of them is NULL.

Furthermore, when both arguments are not equal and both are non-null, it returns the first argument.

Here is the basic syntax:
NULLIF(argument_1, argument_2);

How to Use NULLIF Function in PostgreSQL?

• Example #1: When Both Arguments Are Equal

  Let’s consider a simple scenario where both arguments are equal:

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

  The output will be a clear “NULL,” as the NULLIF function detects equality between the two arguments.
• Example #2: When Both Arguments Are Different

  Now, let’s specify different values for both arguments:

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

  This time, the output will be ‘Command’ because the arguments are distinct, and NULLIF() returns the first argument in this case.
• Example #3: Applying NULLIF() to Table Data

  Let’s create a table called std_info with three columns: std_id, std_name, and std_hobbies.

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

  Then, let’s insert some records into it using the INSERT INTO command:

  INSERT INTO std_info(std_name, std_hobbies)
VALUES
('Bob', 'Sports'),
('Caroline’, ''),
('Klaus', 'Traveling'),
('Damon', NULL),
('Stefan', 'Reading Books'),
('Elijah', NULL);


  This action inserts six records into the std_info table, some of which contain NULL values in the std_hobbies column.

  To illustrate the usage of NULLIF() with table data, we’ll combine it with the COALESCE() function to replace blank or NULL values with “Watching TV” in the std_hobbies column:

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

  In this query, we use NULLIF() to identify records where std_hobbies are either NULL or blank. If this condition is met, we replace them with “Hiking” using COALESCE().

  The output shows the functionality of the NULLIF() function. In other words, it shows how it can be used in real-world database scenarios.

Let us know in the comments if you need further help using the NULLIF() function.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

In brief, our Support Techs demonstrated how to use NULLIF function in PostgreSQL.