PostgreSQL: IS NULL Condition

In this PostgreSQL post explains how to use the PostgreSQL IS NULL condition with syntax and examples.

Description

The PostgreSQL IS NULL condition is used to test for a NULL value in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the IS NULL condition in PostgreSQL is:

expression IS NULL

Parameters or Arguments

expression

The value to test whether it is a NULL value.

Note

• If expression is a NULL value, the condition evaluates to TRUE.
• If expression is not a NULL value, the condition evaluates to FALSE.

Example - With SELECT Statement

Let's look at an example of how to use PostgreSQL IS NULL in a SELECT statement:

SELECT *
FROM employees
WHERE first_number IS NULL;

This PostgreSQL IS NULL example will return all records from the employees table where the first_name contains a NULL value.

Example - With INSERT Statement

Next, let's look at an example of how to use PostgreSQL IS NULL in an INSERT statement:

INSERT INTO contacts
(first_name, last_name)
SELECT first_name, last_name
FROM employees
WHERE employee_number IS NULL;

This PostgreSQL IS NULL example will insert records into the contacts table where the employee_number contains a NULL value.

Example - With UPDATE Statement

Next, let's look at an example of how to use PostgreSQL IS NULL in an UPDATE statement:

UPDATE employees
SET status = 'Not Active'
WHERE last_name IS NULL;

This PostgreSQL IS NULL example will update records in the employees table where the last_name contains a NULL value.

Example - With DELETE Statement

Next, let's look at an example of how to use PostgreSQL IS NULL in a DELETE statement:

DELETE FROM employees
WHERE employee_number IS NULL;

This PostgreSQL IS NULL example will delete all records from the contacts table where the employee_number contains a NULL value.