Filtering Data with WHERE in PostgreSQL

The WHERE clause filters rows so that only those matching a condition are returned. Without it, a SELECT returns every row in the table.

Comparison Operators

-- Equals
SELECT * FROM products WHERE price = 29.99;

-- Greater than / less than
SELECT * FROM products WHERE price > 50;
SELECT * FROM products WHERE stock < 10;

-- Not equal
SELECT * FROM products WHERE category <> 'Electronics';

Pattern Matching with LIKE and ILIKE

-- Names starting with 'J'
SELECT * FROM customers WHERE first_name LIKE 'J%';

-- Case-insensitive match (PostgreSQL-specific)
SELECT * FROM customers WHERE email ILIKE '%@gmail.com';

-- Single character wildcard
SELECT * FROM products WHERE sku LIKE 'A_-100';

% matches zero or more characters, while _ matches exactly one. ILIKE is a PostgreSQL extension that performs case-insensitive matching.

IN, BETWEEN, and NULL Checks

-- IN list
SELECT * FROM orders WHERE status IN ('pending', 'processing');

-- BETWEEN range (inclusive)
SELECT * FROM products WHERE price BETWEEN 10 AND 50;

-- NULL checks
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE phone IS NOT NULL;

Combining Conditions with AND / OR

SELECT * FROM products
WHERE category = 'Electronics'
  AND price < 100
  AND stock > 0;

SELECT * FROM customers
WHERE country = 'US' OR country = 'CA';

Common Use Cases

• Searching for users by email or username
• Filtering orders by status, date range, or amount
• Finding rows with missing data (IS NULL)
• Implementing search features with ILIKE patterns