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.
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:
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:
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:
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:
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:
Now, you want to get the percentage of products in stock_1. You might end up writing the following query:
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.
Use NULLIF to handle the division-by-zero case:
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:
You may also want to replace the resulting NULLs with 0s. You can achieve that with COALESCE:
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:
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.
You typically want to filter out those values, and you can achieve that with a simple NULLIF query as follows:
1
SELECT id,name,
2
NULLIF(description, 'Unknown')
3
FROM products
This will produce:
To avoid ignoring unwanted descriptions because of text case issues, you may want to make your query more robust with:
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:
The goal is to calculate the average review score but exclude the 0 values for a more fair result.
You can achieve that with:
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:
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.
