PostgreSQL Comparison Operators

PostgreSQL Comparison Operators

PostgreSQL comparison operators are used to compare two expressions. These operators are essential for filtering query results based on different types of comparisons such as equality, inequality, and range.

---

List of Comparison Operators

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

Operator	Description
=	Equal to
!= or <>	Not equal to
<	Less than
<=	Less than or equal to
>	Greater than
>=	Greater than or equal to

---

Example PostgreSQL Comparison Operator Queries

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

1. Equal To

SELECT customer_id, customer_name
FROM customers
WHERE customer_id = 1;

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

2. Not Equal To

SELECT customer_id, customer_name
FROM customers
WHERE customer_id != 1;

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

3. Less Than

SELECT product_id, product_name
FROM products
WHERE price < 50.00;

This query retrieves the product_id and product_name from the products table where the price is less than 50.00.

4. Less Than or Equal To

SELECT customer_id, customer_name
FROM customers
WHERE customer_id <= 3;

This query retrieves the customer_id and customer_name from the customers table where the customer_id is less than or equal to 3.

5. Greater Than

SELECT customer_id, customer_name
FROM customers
WHERE customer_id > 1;

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

6. Greater Than or Equal To

SELECT product_id, product_name
FROM products
WHERE price >= 50.00;

This query retrieves the product_id and product_name from the products table where the price is greater than or equal to 50.00.

---

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using various comparison 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 Comparison Operators

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

-- Equal To
SELECT product_id, product_name
FROM products
WHERE price = 60.00;

-- Not Equal To
SELECT product_id, product_name
FROM products
WHERE price != 60.00;

-- Less Than
SELECT product_id, product_name
FROM products
WHERE price < 50.00;

-- Less Than or Equal To
SELECT product_id, product_name
FROM products
WHERE price <= 50.00;

-- Greater Than
SELECT product_id, product_name
FROM products
WHERE price > 50.00;

-- Greater Than or Equal To
SELECT product_id, product_name
FROM products
WHERE price >= 50.00;

These queries demonstrate how to use various comparison operators to filter data from the products table, including equality comparisons, inequality comparisons, and range comparisons.

Conclusion

PostgreSQL comparison operators are fundamental tools for comparing two expressions and filtering query results based on different types of comparisons. Understanding how to use these operators and their syntax is essential for effective data retrieval and manipulation in PostgreSQL databases.