PostgreSQL condition AND

PostgreSQL condition AND (also called AND operator) is used to check two or more conditions in SELECT, INSERT, UPDATE or DELETE operator.

The syntax for the AND condition in PostgreSQL

WHERE condition1
AND condition2
…
AND condition_n;

Parameters and arguments of AND condition

• condition1, condition2, condition_n are all conditions that must be met to select records.

Note:

• PostgreSQL condition AND allows you to test 2 or more conditions.
• The PostgreSQL condition AND requires that all conditions (i.e.: condition1, condition2, condition_n) are met to include a record in the resulting set.

Example of an AND condition with the SELECT operator

Consider a few examples that show how to use the AND condition in PostgreSQL.
The first PostgreSQL query for the AND condition includes a SELECT instruction with two conditions.
For example:

SELECT *
FROM products
WHERE product_type = "Clothes"
AND product_id <= 1500;

In this PostgreSQL AND example, all rows from the products table that have a product_type “Clothes” and have a product_id smaller or equal to 1500 will be returned.

Since the SELECT statement uses *, all fields from the products table will be returned in the result set.

Example of a table connection

Our next PostgreSQL example shows how you can use the AND condition to combine multiple tables in a SELECT statement.
For example:

SELECT products.product_id, products.product_name, inventory.quantity
FROM products, inventory
WHERE products.product_id = inventory.product_id
AND products.product_type = 'Hardware';

Although the above SQL works just fine, you traditionally write this SQL as follows using the correct INNER JOIN.
For example:

SELECT products.product_id, products.product_name, inventory.quantity
FROM products
INNER JOIN inventory
ON products.product_id = inventory.product_id
WHERE products.product_type = 'Hardware';

In this PostgreSQL example, the AND condition will return all lines where product_type equals “Hardware”. And the product and inventory tables are combined into a product_id. You will notice that all fields are prefixed with table names (that is: products.product_id).

This is necessary to eliminate any ambiguity about which field is referenced; since the same field, the name can exist in both the products and inventory tables.

In this case, the resulting set will only display the fields product_id, product_name, and amount (as specified in the first part of the SELECT instruction). .).

Example of the AND condition with the INSERT operator

The following PostgreSQL example demonstrates how the AND condition can be used in the INSERT operator.
For example:

INSERT INTO contacts
(last_name, first_name)
SELECT last_name, first_name
FROM customers
WHERE customer_id > 3000
AND last_name = 'Hard';

In this PostgreSQL example, the AND conditions will be inserted into the contacts table all last_name and first_name records from the table of customers whose customer_id is greater than 3000 and whose last_name is “Hard”.

Example of a condition with UPDATE operator

This PostgreSQL example shows how the AND condition in UPDATE can be used.
For example:

UPDATE contacts
SET last_name = 'Hard'
WHERE contact_id > 500
AND first_name = 'Jack';

In this PostgreSQL example, the AND condition will update all last_name values in the contacts table to ‘Hard’, where contact_id is over 500 and first_name is ‘Jack’.

Example of a condition with the DELETE operator

Finally, the last example of PostgreSQL AND demonstrates how the AND condition can be used in a DELETE statement.
For example:

DELETE FROM contacts
WHERE last_name = 'Hard'
AND first_name = 'Jack';

In this PostgreSQL example, the AND condition will delete all records from the contacts table, where last_name is ‘Hard’ and first_name is ‘Jack’.

Postgres Conditionals: How to Use Case