In Postgres, the IS NULL operator allows us to filter out the NULL values, ensuring that our results contain only the relevant data. While the IS NOT NULL operator opposes the working of the IS NULL operator. This means the IS NOT NULL operator checks the NON NULL values in INSERT, SELECT, DELETE, and UPDATE queries. With these operators, we can filter out NULL and NON-NULL values and get relevant, accurate, and truly representative data results.

This blog post will demonstrate the usage of the IS NOT NULL operator using suitable examples.

How to Use the IS NOT NULL Operator in Postgres?

The basic syntax to use the IS NOT NULL Operator in Postgres is shown in the following snippet:

col_name | expression IS NOT NULL

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

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

We have created a sample table and inserted some null and non-null records into it:

SELECT * FROM emp_data
ORDER BY emp_id;

Now we will use the IS NOT NULL operator to get the non-null values only:

SELECT *
FROM emp_data
WHERE emp_salary IS NOT NULL;

In this example, the IS NOT NULL operator is used to check the existence of NON NULL values within the “emp_salary” column:

The IS NOT NULL operator excludes the NULL values and retrieves the filtered data.

Example 2: How Insert NON-NULL Values From One Table to Another?

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

Now execute the “SELECT *” query one more time to get the details regarding the “emp_data” table:

Now, to insert the NON NULL records from the emp_data table to the emp_information table, we will use the IS NOT NULL operator as follows:

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

To verify the newly inserted non-null entries, use the “SELECT *” command as follows:

SELECT * FROM emp_information;

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

Example 3: How to Update the NON NULL Values in a Postgres Table?

To update the non-null values with some new values, we will use the IS NOT NULL operator with the UPDATE query as follows:

UPDATE emp_data
SET emp_salary = 0
WHERE emp_salary IS NOT NULL;

The above-given code will set a value zero for all the non-null entries in the emp_salary column:

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

SELECT * FROM emp_data;

The output shows that the non-null values have been replaced with a numeric value “0.00”.

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

Use the IS NOT NULL Operator with the DELETE query to delete the non-null values from a Postgres table:

DELETE FROM emp_data
WHERE emp_salary IS NOT NULL;

In the above snippet, the IS NOT NULL operator is used within the WHERE clause to filter the table’s data based on the NON-NULL values. The DELETE query will delete all the NON-NULL entries from the emp_salary column:

The “DELETE 4” message in the output window demonstrates that four records have been deleted from the “emp_data” table. You can verify the deletion of the non-null values using the “SELECT *” command:

SELECT * FROM emp_data;

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

That’s all from this Postgres blog.

Conclusion

In Postgres, the IS NOT NULL operator tests whether an expression or a column contains a non-null value. It can be used in a SELECT statement to identify and return only non-null records from a specific column. Moreover, it can be used with INSERT, UPDATE, and DELETE statements to manipulate the data accordingly. This Postgres blog explained the usage of the “IS NOT NULL” operator with suitable examples.