Summary: this tutorial shows you how to use PostgreSQL NULLIF function to handle null values. We will show you some examples of using the NULLIF function.
PostgreSQL NULLIF function syntax
The NULLIF function is one of the most common conditional expressions provided by PostgreSQL. The following illustrates the syntax of the NULLIF function:
1 | NULLIF(argument_1,argument_2); |
The NULLIF function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1.
See the following examples:
1 2 3 4 5 6 7 8 | SELECT NULLIF (1, 1); -- return NULL SELECT NULLIF (1, 0); -- return 1 SELECT NULLIF ('A', 'B'); -- return A |
PostgreSQL NULLIF function example
Let’s take a look at an example of using the NULLIF function.
First, we create a table named posts as follows:
1 2 3 4 5 6 7 8 | CREATE TABLE posts ( id serial primary key, title VARCHAR (255) NOT NULL, excerpt VARCHAR (150), body TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP ); |
Second, we insert some sample data into the posts table.
1 2 3 4 5 | INSERT INTO posts (title, excerpt, body) VALUES ('test post 1','test post excerpt 1','test post body 1'), ('test post 2','','test post body 2'), ('test post 3', null ,'test post body 3'); |
Third, our goal is to display the posts overview page that shows title and excerpt of each posts. In case the excerpt is not provided, we use the first 40 characters of the post body. We can simply use the following query to get all rows in the posts table.
1 2 3 4 5 6 | SELECT ID, title, excerpt FROM posts; |
We see the null value in the excerpt column. To substitute this null value, we can use the COALESCE function as follows:
1 2 3 4 5 6 | SELECT id, title, COALESCE (excerpt, LEFT(body, 40)) FROM posts; |
Unfortunately, there is mix between null value and ” (empty) in the excerpt column. This is why we need to use the NULLIF function:
1 2 3 4 5 6 7 8 9 | SELECT id, title, COALESCE ( NULLIF (excerpt, ''), LEFT (body, 40) ) FROM posts; |
Let’s examine the expression in more detail:
- First, the
NULLIFfunction returns a null value if the excerpt is empty, otherwise it returns the excerpt. The result of theNULLIFfunction is used by theCOALESCEfunction. - Second, the
COALESCEfunction checks if the first argument, which is provided by theNULLIFfunction, if it is null, then it returns the first 40 characters of the body; otherwise it returns the excerpt in case the excerpt is not null.
Use NULLIF to prevent division-by-zero error
Another great example of using the NULLIF function is to prevent division-by-zero error. Let’s take a look at the following example.
First, we create a new table named members:
1 2 3 4 5 6 | CREATE TABLE members ( ID serial PRIMARY KEY, first_name VARCHAR (50) NOT NULL, last_name VARCHAR (50) NOT NULL, gender SMALLINT NOT NULL -- 1: male, 2 female ); |
Second, we insert some rows for testing:
1 2 3 4 5 6 7 8 9 | INSERT INTO members ( first_name, last_name, gender ) VALUES ('John', 'Doe', 1), ('David', 'Dave', 1), ('Bush', 'Lily', 2); |
Third, if we want to calculate the ratio between male and female members, we use the following query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT (SUM ( CASE WHEN gender = 1 THEN 1 ELSE 0 END ) / SUM ( CASE WHEN gender = 2 THEN 1 ELSE 0 END ) ) * 100 AS "Male/Female ratio" FROM members; |
To calculate the total number of male members, we use the SUM function and CASE expression. If the gender is 1, the CASE expression returns 1, otherwise it returns 0; the SUM function is used to calculate total of male members. The same logic is also applied for calculating the total number of female members.
Then the total of male members is divided by the total of female members to return the ratio. In this case, it returns 200%, which is correct .
Fourth, let’s remove the female member:
1 2 3 4 5 | DELETE FROM members WHERE gender = 2; |
And execute the query to calculate the male/female ratio again, we got the following error message:
1 | [Err] ERROR: division by zero |
The reason is that the number of female is zero. To prevent this division by zero error, we use the NULLIF function as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | SELECT ( SUM ( CASE WHEN gender = 1 THEN 1 ELSE 0 END ) / NULLIF ( SUM ( CASE WHEN gender = 2 THEN 1 ELSE 0 END ), 0 ) ) * 100 AS "Male/Female ratio" FROM members; |
The NULLIF function checks if the number of female members is zero, it returns null. The total of male members is divided by a null value returns a null value, which is correct.
In this tutorial, we have shown you how to apply the NULLIF function to substitute the null values for displaying data and preventing division by zero error.