PostgreSQL IS NULL

Summary: in this tutorial, you will learn how to use the PostgreSQL IS NULL operator to check if a value is NULL or not.

Introduction to NULL

In the database world, NULL means missing information or not applicable. NULL is not a value, therefore, you cannot compare it with any other values like numbers or strings.

The comparison of NULL with a value will always result in NULL. Additionally, NULL is not equal to NULL so the following expression returns NULL:

SELECT null = null AS result;Code language: SQL (Structured Query Language) (sql)

Output:

 result
--------
 null
(1 row)Code language: JavaScript (javascript)

IS NULL operator

To check if a value is NULL or not, you cannot use the equal to (=) or not equal to (<>) operators. Instead, you use the IS NULL operator.

Here’s the basic syntax of the IS NULL operator:

value IS NULLCode language: PHP (php)

The IS NULL operator returns NULL if the value is NULL or false otherwise.

To negate the IS NULL operator, you use the IS NOT NULL operator:

value IS NOT NULLCode language: PHP (php)

The IS NOT NULL operator returns true if the value is not null or false otherwise.

For dealing with NULL in sorting, check out the ORDER BY tutorial.

PostgreSQL offers some functions that deal with NULL including NULLIF, ISNULL, and COALESCE.

To ensure a column that does not contain NULL, you can use the NOT NULL constraint.

PostgreSQL IS NULL operator examples

We’ll use the address table from the sample database:

The psql displays NULL as an empty string by default. To change how psql shows the null in the terminal, you can use the command: \pset null null. It will display NULL as null.

1) Basic IS NULL operator example

The following example uses the IS NULL operator to find the addresses from the address table that the address2 column contains NULL:

SELECT 
  address, 
  address2 
FROM 
  address 
WHERE 
  address2 IS NULL;Code language: SQL (Structured Query Language) (sql)

Output:

       address        | address2
----------------------+----------
 47 MySakila Drive    | null
 28 MySQL Boulevard   | null
 23 Workhaven Lane    | null
 1411 Lillydale Drive | null
(4 rows)Code language: JavaScript (javascript)

2) Using the IS NOT NULL operator example

The following example uses the IS NOT NULL operator to retrieve the address that has the address2 not null:

SELECT 
  address, 
  address2 
FROM 
  address 
WHERE 
  address2 IS NOT NULL;Code language: PHP (php)

Output:

                address                 | address2
----------------------------------------+----------
 1913 Hanoi Way                         |
 1121 Loja Avenue                       |
 692 Joliet Street                      |
 1566 Inegl Manor                       |

Notice that the address2 is empty, not NULL. This is a good example of a bad practice when it comes to storing empty strings and NULL in the same column.

To fix it, you can use the UPDATE statement to change the empty strings to NULL in the address2 column, which you will learn in the UPDATE tutorial.

Summary

• In databases, NULL means missing information or not applicable.
• Use the IS NULL operator to check whether a value is NULL or not.