PostgreSQL WHERE & Filtering - PostgreSQL Tutorial

SQL

SELECT * FROM books WHERE price < 500;

SELECT * FROM books WHERE author = 'Narayan Wagle';

SELECT * FROM books WHERE price >= 300 AND price <= 800;

Operators

= <> (or !=) < > <= >=

BETWEEN, IN

SQL

SELECT * FROM books WHERE price BETWEEN 300 AND 800;   -- inclusive

SELECT * FROM books WHERE author IN ('Narayan Wagle', 'Buddhisagar');

SELECT * FROM books WHERE author NOT IN ('Unknown');

Pattern matching

SQL

SELECT * FROM books WHERE title LIKE 'Palpasa%';    -- starts with
SELECT * FROM books WHERE title LIKE '%Cafe';       -- ends with
SELECT * FROM books WHERE title LIKE '%pas%';       -- contains
SELECT * FROM books WHERE title ILIKE '%CAFE%';     -- case-insensitive

% matches any number of characters, _ matches exactly one. ILIKE is a Postgres extension and is what you usually want for user-facing search.

NULL

NULL means "unknown", and comparing with it always yields NULL — never true:

SQL

-- WRONG: returns nothing, ever
SELECT * FROM books WHERE author = NULL;

-- RIGHT
SELECT * FROM books WHERE author IS NULL;
SELECT * FROM books WHERE author IS NOT NULL;

This also affects NOT IN with NULLs, which silently returns no rows — a subtle and common bug:

SQL

-- if the subquery returns any NULL, this returns nothing at all
SELECT * FROM books WHERE author_id NOT IN (SELECT id FROM authors);

-- safer
SELECT * FROM books b
WHERE NOT EXISTS (SELECT 1 FROM authors a WHERE a.id = b.author_id);

Combining conditions

SQL

SELECT * FROM books
WHERE (author = 'Buddhisagar' OR author = 'Narayan Wagle')
  AND price < 600;

Parentheses matter — AND binds tighter than OR, so without them the query means something quite different.

Date filtering

SQL

SELECT * FROM books WHERE published >= '2020-01-01';

SELECT * FROM books WHERE published >= now() - INTERVAL '30 days';

SELECT * FROM books WHERE EXTRACT(YEAR FROM published) = 2024;

Prefer a plain range (published >= '2024-01-01' AND published < '2025-01-01') over EXTRACT — a function applied to the column prevents Postgres from using an index on it.