By the end of this lesson you can filter rows with every comparison operator, predict how numbers, text, dates and timestamps compare, and compare two columns of the same row.
Why it matters: "Orders over SAR 500", "customers who joined since September", "parcels delivered after the promised date": most business filters are comparisons. Knowing how each data type compares stops quiet mistakes such as missing a whole day of orders.
Dataset: Wadi Market online store. Examples run in the editor on the right.
Equality is only one way to compare. PostgreSQL has six comparison operators, and each produces true or false for every row:
| operator | meaning |
|---|---|
= | equal to |
<> or != | not equal to |
< / > | less than / greater than |
<= / >= | less than or equal / greater than or equal |
<> is the standard spelling of not equal; != means the same in PostgreSQL. Pay attention to whether the boundary is included: unit_price > 1000 excludes a product priced exactly 1000.00, while >= 1000 includes it. Business requests are often vague here ("over 1,000"), so decide deliberately and say which you used.
SELECT product_name, unit_price
FROM products
WHERE unit_price < 100;Keeps products whose list price is strictly below SAR 100: a dozen of the 61 products. A product priced exactly 100.00 would not be included; use <= 100 for that.
SELECT DISTINCT status
FROM orders
WHERE status <> 'delivered';<> keeps rows whose status is anything other than 'delivered'. DISTINCT then lists the remaining statuses once each: pending, paid, shipped, cancelled and returned (in no guaranteed order).
How each data type compares
- Numbers compare by value:
9 < 100. - Text compares character by character, like a dictionary, so
'Abha' < 'Buraidah'. In this database (and many servers) the comparison uses character codes, so all capitals sort before all lowercase letters:'Zulfi' < 'abha'is true. Digits stored as text compare as text too:'100' < '9'is true, because the first character '1' comes before '9'. Numbers stored in a text column are a classic source of wrong filters. - Dates compare chronologically. Write date values as
'YYYY-MM-DD'text, for example'2025-09-01'; PostgreSQL converts the text to a date because the column is a date. You can be explicit withDATE '2025-09-01'. - Timestamps (date plus time, like
orders.ordered_at) compare to the second. A date-only value such as'2025-12-31'means midnight at the start of that day,2025-12-31 00:00:00.
SELECT 'Abha' < 'Buraidah' AS alphabetical,
'Zulfi' < 'abha' AS capitals_first,
'100' < '9' AS text_digits,
100 < 9 AS real_numbers,
TIMESTAMP '2025-12-31 15:00' = '2025-12-31' AS same_day_is_not_equal;Each column is a true/false result. Text compares character by character, so '100' < '9' is true while the numbers 100 < 9 is false. The last column is false because '2025-12-31' means midnight, and 15:00 that afternoon is a different moment.
SELECT customer_id, first_name, last_name, signup_date
FROM customers
WHERE signup_date >= '2025-12-01';signup_date is a date column, so '2025-12-01' is read as a date and the comparison is chronological. >= includes customers who signed up on 1 December itself. Since the data ends on 31 December 2025, this lists the most recent sign-ups.
Comparing two columns, or a column with a calculation
Both sides of a comparison can be expressions. WHERE delivered_at >= promised_date + 7 compares two columns of the same row: for each parcel, did it arrive at least a week after the date promised for that parcel? The same idea finds thin margins (cost compared with a share of the price) or discounts larger than the line value.
SELECT shipment_id, carrier, promised_date, delivered_at
FROM shipments
WHERE delivered_at >= promised_date + 7;For each parcel, PostgreSQL adds 7 days to that row's promised_date and compares the result with that row's delivery timestamp. Only badly delayed parcels remain. Parcels not delivered yet (delivered_at is NULL) are not returned: comparing NULL with anything is not true, a rule you'll study in the NULL lesson.