{"id":711,"date":"2024-12-07T18:46:09","date_gmt":"2024-12-07T11:46:09","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=711"},"modified":"2025-01-03T16:16:08","modified_gmt":"2025-01-03T09:16:08","slug":"postgresql-nullif","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-nullif\/","title":{"rendered":"PostgreSQL NULLIF Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL NULLIF<\/code> function to compare two expressions and return NULL<\/code> if they are equal.<\/p>\n\n\n\n

Getting started with the PostgreSQL NULLIF function #<\/a><\/h2>\n\n\n\n

In PostgreSQL, the NULLIF<\/code> function compares two expressions and returns NULL<\/a><\/code> if they are equal. Otherwise, it returns the result of the first expression.<\/p>\n\n\n\n

Here’s the syntax of the NULLIF<\/code> function:<\/p>\n\n\n

NULLIF(expression1, expression2)<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If expression1<\/code> equals expression2<\/code>, the function returns NULL<\/code>. Otherwise, it returns expression1<\/code>.<\/p>\n\n\n\n
Note that the NULLIF<\/code> function compares the results of two expressions using the equal operator (=<\/code>). This means that the results of both expressions must be comparable. In other words, they must have a compatible data type for comparison.<\/p>\n\n\n\n
Basic PostgreSQL NULLIF function examples #<\/a><\/h2>\n\n\n\n
The following example returns NULL<\/code> because both values are equal:<\/p>\n\n\n
SELECT<\/span> NULLIF(0<\/span>, 0<\/span>) result;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output<\/p>\n\n\n
 result\n--------\n   NULL<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example returns 1 because 1 is not equal to 0:<\/p>\n\n\n
SELECT<\/span> NULLIF(1<\/span>, 0<\/span>) result;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output<\/p>\n\n\n
 result\n--------\n      1<\/code><\/span><\/pre>\n\n\n
The following example always returns NULL<\/code> because NULL<\/code> is not equal to NULL<\/code>.<\/p>\n\n\n
SELECT<\/span> NULLIF(NULL<\/span>, NULL<\/span>) result;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------\n NULL<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example returns when comparing a string ‘1’ with the number 1:<\/p>\n\n\n
SELECT<\/span> NULLIF('1'<\/span> , 1<\/span>) result;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------\n   NULL<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
It returns the same result as you compare the ‘1’ and 1 using the equal operator:<\/p>\n\n\n
SELECT<\/span> '1'<\/span> = 1<\/span> result;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output<\/p>\n\n\n
 result\n--------\n t<\/code><\/span><\/pre>\n\n\n
The ‘1’ is equal 1, so the ‘1’= 1 returns true<\/code>. The NULLIF('1', 1)<\/code> returns NULL<\/code>.<\/p>\n\n\n\n
Using PostgreSQL NULLIF function to replace a value with NULL #<\/a><\/h2>\n\n\n\n
The NULLIF<\/code> function can help replace a value with NULL<\/code>, especially for data cleansing.<\/p>\n\n\n\n
In the application, users may enter an empty string (''<\/code>) or N\/A <\/code>into a column into a nullable column.<\/p>\n\n\n\n
To fix this issue, you need to validate the data at the application layer and clean up data at the database level; you can use the NULLIF<\/code> function to replace these invalid values with NULL<\/code>.<\/p>\n\n\n\n
Suppose you have the following contacts<\/code> table that store contact id, name, phone and email:<\/p>\n\n\n\n
SQL Script for creating the contact table and insert data into the table<\/summary>
CREATE<\/span> TABLE<\/span> contacts(\n    contact_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    name<\/span> VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n    phone VARCHAR<\/span>(25<\/span>),\n    email VARCHAR<\/span>(255<\/span>)\n);\nINSERT<\/span> INTO<\/span> contacts (name<\/span>, phone, email) \nVALUES<\/span>\n('Alice Johnson'<\/span>, 'N\/A'<\/span>, ''<\/span>),\n('Bob Smith'<\/span>, '123-456-789'<\/span>, 'bob.smith@pgtutorial.com'<\/span>),\n('Carol White'<\/span>, '123-456-788'<\/span>, 'carol.white@pgtutorial.com'<\/span>),\n('David Brown'<\/span>, '123-456-787'<\/span>, 'david.brown@pgtutorial.com'<\/span> ),\n('Eve Black'<\/span>, '123-456-786'<\/span>, ''<\/span>)\nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre><\/details>\n\n\n\n
contact_id<\/th>name<\/th>phone<\/th>email<\/th><\/tr><\/thead>
1<\/td>Alice Johnson<\/td>‘N\/A’<\/td> ”<\/td><\/tr>
2<\/td>Bob Smith<\/td>123-456-789<\/td>bob.smith@pgtutorial.com<\/td><\/tr>
3<\/td>Carol White<\/td>123-456-788<\/td>carol.white@pgtutorial.com<\/td><\/tr>
4<\/td>David Brown<\/td>123-456-787<\/td>david.brown@pgtutorial.com<\/td><\/tr>
5<\/td>Eve Black<\/td>123-456-786<\/td> ”<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
We can use an UPDATE<\/code> statement and NULLIF<\/code> function to update the invalid values in the phone and email columns as follows:<\/p>\n\n\n
UPDATE<\/span> contacts\nSET<\/span>\n  phone = NULLIF(phone, 'N\/A'<\/span>),\n  email = NULLIF(email, ''<\/span>) \nRETURNING<\/span> *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Output:<\/p>\n\n\n
 contact_id |     name      |    phone    |           email\n------------+---------------+-------------+----------------------------\n          1<\/span> | Alice Johnson | NULL<\/span>        | NULL<\/span>\n          2<\/span> | Bob Smith     | 123<\/span>-456<\/span>-789<\/span> | bob.smith@pgtutorial.com\n          3<\/span> | Carol White   | 123<\/span>-456<\/span>-788<\/span> | carol.white@pgtutorial.com\n          4<\/span> | David Brown   | 123<\/span>-456<\/span>-787<\/span> | david.brown@pgtutorial.com\n          5<\/span> | Eve Black     | 123<\/span>-456<\/span>-786<\/span> | NULL<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Preventing the division by zero error #<\/a><\/h2>\n\n\n\n
The following statement retrieves the safety stock from the products<\/code> table joins it with inventory quantity from the inventories<\/code> table, and calculates the stock cover ratio:<\/p>\n\n\n
WITH<\/span>\n  stocks AS<\/span> (\n    SELECT<\/span>\n      product_name,\n      safety_stock,\n      SUM(quantity) inventory_qty\n    FROM<\/span>\n      products\n      INNER<\/span> JOIN<\/span> inventories USING<\/span> (product_id)\n    GROUP<\/span> BY<\/span>\n      product_name,\n      safety_stock\n  )\nSELECT<\/span>\n  product_name,\n  inventory_qty \/ safety_stock stock_coverage\nFROM<\/span>\n  stocks\nORDER<\/span> BY<\/span>\n  product_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
division by zero<\/code><\/span><\/pre>\n\n\n
The output shows the division by zero error.<\/p>\n\n\n\n
The reason is that some products have safety stock 0. To prevent the division by zero error, you can use the NULL<\/code>IF statement to return NULL<\/code> if the safety stock is zero:<\/p>\n\n\n
WITH<\/span>\n  stocks AS<\/span> (\n    SELECT<\/span>\n      product_name,\n      NULLIF(safety_stock, 0<\/span>) safety_stock,\n      SUM(quantity) inventory_qty\n    FROM<\/span>\n      products\n      INNER<\/span> JOIN<\/span> inventories USING<\/span> (product_id)\n    GROUP<\/span> BY<\/span>\n      product_name,\n      safety_stock\n  )\nSELECT<\/span>\n  product_name,\n  inventory_qty,\n  safety_stock,\n  inventory_qty \/ safety_stock stock_coverage\nFROM<\/span>\n  stocks\nORDER<\/span> BY<\/span>\n  product_name;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Partial Output:<\/p>\n\n\n
        product_name        | inventory_qty | safety_stock | stock_coverage\n----------------------------+---------------+--------------+----------------\n Apple AirPods Pro 3        |           180 |           45 |              4\n Apple iMac 24\"             |           320 |         NULL |           NULL\n Apple iPad Pro 12.9        |           170 |           15 |             11\n Apple iPhone 15            |           150 |           20 |              7\n Apple iPhone 15 Pro Max    |           140 |           50 |              2\n Apple Watch Series 9       |           200 |           20 |             10\n Bose SoundLink Max         |           270 |         NULL |           NULL\n...<\/code><\/span><\/pre>\n\n\n
In this example, if the safety stock is 0, the NULLIF<\/code> statement returns NULL<\/code>; otherwise, it returns the safety stock. Dividing an inventory by NULL<\/code> will result in NULL<\/code> instead of an error.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n