PostgreSQL IS NULL operator - Check if a value is a null value

PostgreSQL IS NULL is a boolean operator that checks if a value is NULL. A null value is a special value that means nothing, it is neither the empty string nor false.

PostgreSQL IS NULL syntax

PostgreSQL IS NULL is an unary comparison operator that requires only one operand. This is the syntax of the IS NULL operator:

expr IS NULL
expr IS NOT NULL

Explanation:

• The expr can be a column name, a value, or an expression.
• The IS NOT NULL is the negation of IS NULL.

IS NULL and IS NOT NULL can be used in SELECT statements or WHERE clauses.

PostgreSQL IS NULL Rules

If the operand of the PostgreSQL IS NULL operator is a null value, the IS NULL operator returns true, otherwise it returns false.

SELECT
    NULL IS NULL "NULL IS NULL",
    0 IS NULL "0 IS NULL",
    1 IS NULL "1 IS NULL";

 NULL IS NULL | 0 IS NULL | 1 IS NULL
--------------+-----------+-----------
 t            | f         | f

IS NOT NULL is the negation of IS NULL. The IS NOT NULL operator returns true if the operand is not a null value, otherwise returns false.

SELECT
    NULL IS NOT NULL "NULL IS NOT NULL",
    0 IS NOT NULL "0 IS NOT NULL",
    1 IS NOT NULL "1 IS NOT NULL";

 NULL IS NOT NULL | 0 IS NOT NULL | 1 IS NOT NULL
------------------+---------------+---------------
 f                | t             | t

PostgreSQL IS NULL Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

In the Sakila sample database, the staff table stores information about employees in a DVD rental store.

In the staff table, the picture column stores the photo files of employees. To query those employees who have not uploaded pictures from the staff table, you need to check whether picture is NULL, please use the following statement with IS NULL:

SELECT
    first_name, last_name, picture
FROM
    staff
WHERE
    picture IS NULL;

 first_name | last_name | picture
------------+-----------+---------
 Mike       | Hillyer   | <null>
 Jon        | Stephens  | <null>

Conclusion

In this article, we learned the syntaxs and usages of the IS NULL and IS NOT NULL operators in PostgreSQL. The main points of this article are as follows:

• The IS NULL and the IS NOT NULL are unary comparison operators.
• Use the IS NULL operator to check if a value is null.
• The IS NOT NULL operator is the negation of IS NULL.
• The result of NULL IS NULL is true.