In PostgreSQL, NULL is a special marker that represents missing information or a non-existent value rather than an empty string or zero. A table in Postgres may or may not have some null entries. Postgres' IS NULL operator lets you filter and manipulate data based on whether a given column contains a NULL value or not. The IS NULL operator is especially useful when working with large datasets, where finding missing information can be essential for making data-driven decisions.

This blog will show you the usage of the IS NULL operator via practical examples.

IS NULL Operator/Condition in PostgreSQL

In Postgres, the IS NULL operator tests whether an expression or a column contains a null value or not. It can be used in a SELECT statement to identify and return the rows with missing values in a specific column.

It can also be used in INSERT, UPDATE, and DELETE statements to manipulate the data accordingly. For instance, you can use the IS NULL operator in an UPDATE statement's SET clause to update only the rows where a column is null.

The IS NULL operator can be used with other comparison operators such as =, >=, >, etc. In such cases, it provides even more control over your data. For instance, you can use it to find records where a certain column does not have a NULL value and meet certain criteria.

How to Use the IS NULL Operator in Postgres?

The basic syntax to use the IS NULL Operator in Postgres is as follows:

col_name | expression IS NULL

Specify the column name or expression of your choice in which you want to check the existence of the NULL values. The IS NULL operator retrieves TRUE if the null value is found in a column/expression else, it retrieves FALSE.

Example 1: How to Find the NULL Values in a Postgres Table?

We have created a sample table and inserted some records into it. The table’s data contains some null values as well:

SELECT * FROM emp_data
ORDER BY emp_id;

Let’s use the IS NULL operator and see how it works:

SELECT *
FROM emp_data
WHERE emp_salary IS NULL;

The above statement will check the existence of NULL values within the “emp_salary” column:

The IS NULL operator retrieves the filtered data based on the NULL values.

Example 2: How to Use IS NULL Operator With INSERT Statement?

We have created a couple of tables named “emp_data” and “emp_information”, whose data is shown in the following snippets:

Now execute the “SELECT *” command to get the details of the “emp_data” table:

Let's say we want to insert the records with null values from the emp_data table into the emp_information table. For this purpose, we will execute the following statements:

INSERT INTO emp_information(emp_name)
SELECT emp_name
FROM emp_data 
WHERE emp_salary IS NULL;

Three records with “null” values have been inserted from the “emp_data” table to the “emp_information” table. To verify the newly inserted null entries, use the “SELECT *” command:

SELECT * FROM emp_information;

This is how the IS NULL operator works with Postgres’ INSERT statement.

Example 3: How to Find and Update the NULL Values From a Postgres Table?

Suppose we want to update the null values with some new values. For this purpose, we will use the ISNULL operator with the UPDATE query as follows:

UPDATE emp_information
SET emp_salary = 0
WHERE emp_salary IS NULL;

The above-given code will replace the null values with “0” in the emp_salary column:

Now, execute the “SELECT *” command one more time to see the updated table’s records:

SELECT * FROM emp_information;

This is how the IS NULL operator works with the UPDATE query.

Example 4: How to Find and Delete the NULL Values From a Postgres Table?

Use the IS NULL Operator with the DELETE query to delete the null entries from a table in Postgres:

DELETE FROM emp_data
WHERE emp_salary IS NULL;

In the above snippet, the IS NULL operator is used in the WHERE clause to filter the table’s data based on the NULL values. The DELETE query will delete the filtered NULL values from the emp_salary column:

The “DELETE 3” message in the output shows that three records have been deleted from the “emp_data” table. Let’s verify the deletion of the null entries via the following command:

SELECT * FROM emp_data;

The output shows that the “emp_data” table contains only non-null values. It proves that the null entries have been deleted successfully.

Conclusion

In Postgres, the IS NULL operator tests whether an expression or a column contains a null value or not. It can be used in a SELECT statement to identify and return only rows with missing values in a specific column. Moreover, it can be used in INSERT, UPDATE, and DELETE statements to manipulate the data accordingly. This Postgres blog explains what the “IS NULL” operator is and how it works in Postgres.