PostgreSQL NOT condition

The PostgreSQL NOT condition (also called the NOT operator) is used to deny the condition in SELECT, INSERT, UPDATE or DELETE.

The syntax for NOT condition in PostgreSQL

NOT condition

Parameters and arguments of the condition

• Condition – Denial of condition.

Note:

• The PostgreSQL NOT condition requires that the opposite condition is met so that the record is included in the result set.

Example of a condition in combination with the IN condition

NOT condition for PostgreSQL can be combined with IN condition.
For example:

SELECT *
FROM empls
WHERE last_name NOT IN ('Ivon', 'Abram', 'Skin');

This PostgreSQL NOT example would return all rows from the employee table where last_name is not, ‘Ivon’, ‘Abram’, or ‘Skin’. Sometimes it is more efficient to list the values that you don’t want, as opposed to the values that you want.

Example of a condition in combination with the IS NULL condition

PostgreSQL condition NOT can also be combined with the IS NULL condition.
For example,

SELECT *
FROM contacts
WHERE address IS NOT NULL;

This example of PostgreSQL NOT would return all records from the contacts table where the address does not contain a value of NULL.

Example of a condition in combination with the LIKE condition

The NOT condition in PostgreSQL can also be combined with the LIKE condition.
For example:

SELECT product_name, product_description
FROM products
WHERE product_name NOT LIKE 'H%';

By placing the PostgreSQL operator NOT before the LIKE condition, you can get all records of products whose product_name does not start with ‘H’.

Example of a condition in combination with the BETWEEN condition

The NOT condition in PostgreSQL can also be combined with the BETWEEN condition. Here is an example of how you could combine a NOT operator with a BETWEEN condition.
For example:

SELECT *
FROM empls
WHERE empl_id NOT BETWEEN 525 AND 600;

This PostgreSQL example NOT would return all rows from an employee table where employee_id is not between 525 and 600 inclusive. This would be equivalent to the next SELECT operator:

SELECT *
FROM empls
WHERE empl_id < 525
OR empl_id > 600;

Example of a condition in combination with the EXISTS condition

The NOT condition in PostgreSQL can also be combined with the EXISTS condition.
For example,

SELECT *
FROM products
WHERE DOES NOT EXIST (SELECT 1
FROM inventory
WHERE products.product_id = inventory.product_id);

In this PostgreSQL NOT example, all records from the products table that have no records from the inventory table for this product_id will be returned.)

Postgres Conditionals: How to Use Case