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.
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 |
Let's look at some examples of PostgreSQL comparison operator queries:
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.
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.
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.
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.
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.
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.
Let's go through a complete example that includes creating a table, inserting data, and using various comparison operators to filter data.
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
.
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.
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.
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.