NULLIF
POSTGRESQL

PostgreSQL NULLIF: Conditional Logic Made Easier

intro

Summary: Let’s explore the NULLIF PostgreSQL function, taking a look at what it is, what it does, and when to use it in some real-world examples.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

In most cases, you have to handle NULL values by replacing them with more appropriate options. In other cases, you need to do the opposite, which is to convert non-NULL values into NULLs to deal with specific scenarios. How to do that directly in your PostgreSQL queries? With NULLIF!

In this article, you will find out everything you need to know about the PostgreSQL NULLIF function and see how it can help you build NULL-safe applications.

What Is NULLIF in PostgreSQL?

NULLIF is a PostgreSQL function that accepts two arguments and returns a NULL if the two arguments are equal. Otherwise, it returns the first argument. To be specific, PostgreSQL NULLIF returns a NULL when the = operator applied to the two parameters passed to the function produces a TRUE value. This function is one of the most common PostgreSQL conditional expressions, along with COALESCE, CASE, GREATEST and LEAST.

How to Use NULLIF in PostgreSQL

You can use the NULLIF function in PostgreSQL by using the syntax below:

Copy
        
1 NULLIF(value_1, value_2)

This expression produces a NULL value when value_1 equals value_2, otherwise it returns value_1. Keep in mind that the two arguments must be of comparable types. In particular, they must be comparable with the = operator in a value1 = value2 expression.

Let’s see NULLIF in action in a sample PostgreSQL query:

Copy
        
1 SELECT COALESCE(age, 0) AS age 2 FROM users

When the age column contains the value 0, the NULLIF function will replace it with a NULL in the result set.

PostgreSQL NULLIF vs COALESCE

COALESCE is one of the most popular functions to handle NULL values in PostgreSQL. It accepts several arguments and returns the first one that is not null. So, what is the relationship between NULLIF and  COALESCE?

Well, NULLIF can be seen as a sort of inverse operation of COALESCE. In detail, the NULLIF PostgreSQL function is primarily used to replace a specific value with NULL, while COALESCE is used to replace non-NULL values with a particular value.

Consider the example below:

Copy
        
1 SELECT COALESCE(description, short_description, 'Missing data') as description 2 FROM products

This returns the description of each product if available, or the “Missing data” string otherwise.

Now, take a look at a similar query with NULLIF:

Copy
        
1 SELECT NULLIF(description, 'Missing data') as description 2 FROM products

In this case, when description contains the “Missing data” string, it becomes NULL.

As you can see, the two functions do exactly the opposite. Their main difference in the syntax is that COALESCE takes many input parameters, while NULLIF is limited to two arguments.

Note that you can use COALESCE inside a NULLIF expression and vice versa.

Use Cases of NULLIF

Let's explore a few real-world scenarios where the NULLIF function can be applied effectively.

Avoid a Division by Zero

When performing division operations in PostgreSQL queries, you may get the following error:

[Err] ERROR:  division by zero

This occurs when the divisor is 0, as it is not possible to divide a number by 0.

To avoid division-by-zero errors, you should protect your query with NULLIF. Let’s learn how with an example.

Suppose you store the stock data of a product in a table:

Note the products with id = 3 and 5, where all "stock" columns are 0
Note the products with id = 3 and 5, where all "stock" columns are 0

Now, you want to get the percentage of products in stock_1. You might end up writing the following query:

Copy
        
1 SELECT id, 2 (stock_1::float / (stock_1 + stock_2 + stock_3)) * 100 AS stock_1_perc 3 FROM products

However, this could lead to a potential division-by-zero error as the total stock for one product might be 0.

Note the "Division by zero" error reported by DbVisualizer
Note the "Division by zero" error reported by DbVisualizer

Use NULLIF to handle the division-by-zero case:

Copy
        
1 SELECT id, ((stock_1 + stock_2 + stock_3) / NULLIF(stock_1, 0)) * 100 AS stock_1_perc 2 FROM products

Now, when stock_1 is 0, the NULLIF function will return NULL, preventing the error. This is because a division by NULL in PostgreSQL produces a NULL value. So, the output of the query will be as follows:

Note the NULL values on the products with id = 3 and 5
Note the NULL values on the products with id = 3 and 5

You may also want to replace the resulting NULLs with 0s. You can achieve that with COALESCE:

Copy
        
1 SELECT id, 2 COALESCE(((stock_1 + stock_2 + stock_3) / NULLIF(stock_1, 0)) * 100, 0) AS stock_1_perc 3 FROM products

Here is the new result set:

NULLs are now 0s
NULLs are now 0s

Great! Mission accomplished.

Ignoring Specific Values

PostgreSQL NULLIF is useful to replace specific values with NULL, making it easier to ignore those values in your queries. For example, assume that some of your products might have an ”Unknown” description.

Note the description on products with id = 4 and 6
Note the description on products with id = 4 and 6

You typically want to filter out those values, and you can achieve that with a simple NULLIF query as follows:

Copy
        
1 SELECT id,name, 2 NULLIF(description, 'Unknown') 3 FROM products

This will produce:

“Unknown” descriptions are now NULLs
“Unknown” descriptions are now NULLs

To avoid ignoring unwanted descriptions because of text case issues, you may want to make your query more robust with:

Copy
        
1 SELECT id,name, 2 NULLIF(LOWER(TRIM(description)), LOWER(TRIM('Unknown'))) AS description 3 FROM products

TRIM() will remove leading or trailing spaces, while LOWER() will convert the strings to lowercase.

Exclude Specific Values in an Operation

NULLIF is also helpful in conditional aggregation to exclude specific occurrences when counting or summing. Suppose you have the reviews of your products on a scale of 0 to 5 stars stored in a product_reviews table:

Note the 0s in the "rating" column
Note the 0s in the "rating" column

The goal is to calculate the average review score but exclude the 0 values for a more fair result.

You can achieve that with:

Copy
        
1 SELECT product_id,(SUM(rating)::float) / COUNT(NULLIF(rating, 0)) AS avg_rating 2 FROM product_reviews 3 GROUP BY product_id 4 ORDER BY product_id

Note the cast from integer to numeric to produce float averages.

This query works because NULLs are excluded in the COUNT operator:

The average was calculated with no 0
The average was calculated with no 0

Fantastic! The NULLIF PostgreSQL function is now part of your toolset!

Conclusion

In this guide, you learned what NULLIF is in PostgreSQL, its syntax, and how to use it in real-world examples. In detail, that function allows you to replace specific values with NULL and represent a sort of inverse operation of COALESCE.

To truly understand the potential of NULLIF, you need a tool that helps you manage databases and visually explore query results. There is where a comprehensive database client like DbVisualizer comes in! In addition to being able to connect to dozens of DBMSs, this tool offers advanced query optimization capabilities and full support for basically almost all PostgreSQL features, including NULLIF. Download DbVisualizer for free now!

FAQ

How efficient is NULLIF?

The efficiency of the NULLIF function in PostgreSQL depends on the context and data being processed. At the same time, it can be considered highly efficient as it simply involves equality operations.

Can the NULLIF function be used in combination with other conditional logic, such as CASE?

Yes, the NULLIF function can be used in conjunction with any other conditional operator or function. For example, it is often used within CASE expressions to handle specific conditions and return appropriate results.

Is there any difference between PostgreSQL NULLIF and ISNULL?

PostgreSQL NULLIF is a standard SQL function that returns NULL if two arguments are equal or the first argument otherwise. On the other hand, ISNULL is not supported by PostgreSQL because it is not a standard SQL function. Different DBMSs provide different implementations of ISNULL.

What happens when using the NULLIF function with more than two arguments?

The NULLIF PostgreSQL function takes exactly two arguments. If more than two arguments are provided, PostgreSQL will raise a syntax error.

What data types NULLIF function work with?

The NULLIF function works with various data types supported by PostgreSQL, such as numeric, integer, text, and more. The result of the NULLIF function will have the same type as the first argument. If required, the second argument gets implicitly cast to make comparison possible through the = operator.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

PostgreSQL Subquery - A Guide

author Leslie S. Gyamfi tags POSTGRESQL SUBQUERY 3 min 2024-10-02
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

A Guide to the Postgres Not Null Constraint

author Leslie S. Gyamfi tags POSTGRESQL 3 min 2024-09-25
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

The Postgres UPDATE Statement: A Deep Dive

author Leslie S. Gyamfi tags POSTGRESQL 4 min 2024-09-05
title

PostgreSQL Sequence: A Beginner’s Guide

author Ochuko Onojakpor tags POSTGRESQL 11 min 2024-09-04
title

Cursors in PostgreSQL - A Guide

author Leslie S. Gyamfi tags Cursors POSTGRESQL 7 min 2024-09-03
title

Everything You Need to Know About the Postgres JSONB Data Type

author Antonello Zanini tags JSON POSTGRESQL 5 min 2024-09-02
title

PostgreSQL ADD COLUMN: Add One or More Columns to a Table

author Leslie S. Gyamfi tags ALTER POSTGRESQL 6 min 2024-08-22
title

Index Creation in SQL

author Lukas Vileikis tags MySQL POSTGRESQL SQL SERVER 7 min 2024-08-15

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗