PostgreSQL Logical Operators

PostgreSQL Logical Operators

PostgreSQL logical operators are used to combine multiple conditions in a query. These operators are essential for filtering query results based on complex conditions that involve logical conjunctions, disjunctions, and negations.

---

List of Logical Operators

Here is a list of the most commonly used PostgreSQL logical operators:

Operator	Description
AND	Logical AND
OR	Logical OR
NOT	Logical NOT

---

Example PostgreSQL Logical Operator Queries

Let's look at some examples of PostgreSQL logical operator queries:

1. Logical AND

SELECT customer_id, customer_name
FROM customers
WHERE customer_id > 1 AND customer_id < 5;

This query retrieves the customer_id and customer_name from the customers table where the customer_id is greater than 1 and less than 5.

2. Logical OR

SELECT customer_id, customer_name
FROM customers
WHERE customer_id = 1 OR customer_id = 3;

This query retrieves the customer_id and customer_name from the customers table where the customer_id is either 1 or 3.

3. Logical NOT

SELECT customer_id, customer_name
FROM customers
WHERE NOT (customer_id = 2);

This query retrieves the customer_id and customer_name from the customers table where the customer_id is not equal to 2.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using various logical operators to filter data.

Step 1: Creating a Table

This step involves creating a new table named products to store product data.

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    price NUMERIC(10, 2)
);

In this example, we create a table named products with columns for product_id, product_name, and price.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the products table.

INSERT INTO products (product_name, price)
VALUES ('Product A', 30.00),
       ('Product B', 60.00),
       ('Product C', 90.00);

Here, we insert data into the products table.

Step 3: Using Logical Operators

This step involves using various logical operators to filter data from the products table.

-- Logical AND
SELECT product_id, product_name
FROM products
WHERE price > 30.00 AND price < 90.00;

-- Logical OR
SELECT product_id, product_name
FROM products
WHERE price = 30.00 OR price = 90.00;

-- Logical NOT
SELECT product_id, product_name
FROM products
WHERE NOT (price = 60.00);

These queries demonstrate how to use various logical operators to filter data from the products table, including logical conjunctions, disjunctions, and negations.

Conclusion

PostgreSQL logical operators are fundamental tools for combining multiple conditions and filtering query results based on complex logical expressions. Understanding how to use these operators and their syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.