PostgreSQL IS NULL Operator

Summary: In this tutorial, you’ll learn to use the PostgreSQL IS NULL operator to determine if a value is NULL.

Introduction to the PostgreSQL IS NULL operator. #

In PostgreSQL, NULL is a marker that represents unknown data. To check if a value is NULL, you cannot compare it with NULL using the equal to operator.

Since NULL is unknown, comparing a value with an unknown value will result in an unknown value (or NULL). For example:

SELECT
  10 = NULL AS result;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 result
--------
 NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, we compare the number 10 with NULL using the equal to operator. The comparison returns NULL.

To compare a value with NULL, you use the IS NULL operator:

value IS NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The IS NULL operator compares the value with NULL and returns true if the value is NULL or false otherwise. For example:

SELECT
  NULL IS NULL null_vs_null,
  10 IS NULL null_vs_10;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

To negate the result of the IS NULL operator, you use the IS NOT NULL operator.

value IS NOT NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

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

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

If you want to handle NULL using functions instead of the IS NULL operator, you can use the NULLIF, ISNULL, and COALESCE functions.

PostgreSQL IS NULL operator example #

We’ll use the profiles table to demonstrate the IS NULL operator:

The following SELECT statement uses the IS NULL operator to retrieve users who do not have work phones:

SELECT
  first_name,
  last_name,
  work_phone
FROM
  profiles
WHERE
  work_phone IS NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 first_name | last_name | work_phone
------------+-----------+------------
 Alice      | Jones     | NULL
 Bob        | Brown     | NULL
 Charlie    | Davis     | NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The query returns the employees with the values in the work_phone are NULL.

PostgreSQL IS NOT NULL example #

The following query uses the IS NOT NULL operator to retrieve employees who have work phones:

SELECT
  first_name,
  last_name,
  work_phone
FROM
  profiles
WHERE
  work_phone IS NOT NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 first_name | last_name |  work_phone
------------+-----------+--------------
 John       | Doe       | 408-456-7890
 Jane       | Smith     | 408-456-7891Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

psql and NULL #

psql is a client program for interacting with PostgreSQL Server using a command-line interface.

The psql program displays blank for NULL by default. To use the NULL literal string to represent NULL, you can use the following command: 

\pset null NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• The IS NULL operator compares a value with NULL and returns true if the value is NULL or false otherwise.
• The IS NOT NULL operator checks a value against NULL and returns true if a value is not NULL or false otherwise.

Quiz #