PostgreSQL: IS NOT NULL

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

Description

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

Syntax

The syntax for the IS NOT NULL condition in PostgreSQL is:

expression IS NOT NULL

Parameters or Arguments

expression

The value to test whether it is a non-NULL value.

Note

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

Example - With SELECT Statement

Here is an example of how to use the PostgreSQL IS NOT NULL condition in a SELECT statement:

SELECT *
FROM employees
WHERE first_name IS NOT NULL;

This PostgreSQL IS NOT NULL example will return all records from the employees table where the first_name does not contain a null value.

Example - With INSERT Statement

Here is an example of how to use the PostgreSQL IS NOT NULL condition in an INSERT statement:

INSERT INTO inventory
(inventory_id, item_name)
SELECT product_id, product_name
FROM products
WHERE product_name IS NOT NULL;

This PostgreSQL IS NOT NULL example will insert records into the inventory table where the product_name does not contain a null value.

Example - With UPDATE Statement

Here is an example of how to use the PostgreSQL IS NOT NULL condition in an UPDATE statement:

UPDATE inventory
SET status = 'Available'
WHERE item_name IS NOT NULL;

This PostgreSQL IS NOT NULL example will update records in the inventory table where the item_name does not contain a null value.

Example - With DELETE Statement

Here is an example of how to use the PostgreSQL IS NOT NULL condition in a DELETE statement:

DELETE FROM employees
WHERE archive IS NOT NULL;

This PostgreSQL IS NOT NULL example will delete all records from the employees table where the archive field does not contain a null value.