Postgres by Example: WHERE

WHERE is how you filter rows. The clause sits between FROM and ORDER BY, and PostgreSQL keeps only rows for which the condition evaluates to true. Anything that evaluates to false or NULL is dropped — this three-valued logic is one of SQL's quirks and the cause of many beginner bugs. Get comfortable with it early.

What you'll learn:

• Filtering with comparison operators (=, <>, <, >, <=, >=)
• Pattern matching with LIKE (and ILIKE for case-insensitive)
• Combining conditions with AND, OR, NOT
• Membership and ranges: IN, BETWEEN
• Why = NULL does not work and what to use instead

-- Equality and ordering
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
ORDER BY tablename
LIMIT 3;

-- Pattern matching: % matches any sequence, _ matches one character
SELECT tablename
FROM pg_tables
WHERE tablename LIKE 'pg_a%'
ORDER BY tablename
LIMIT 5;

-- Case-insensitive: ILIKE (PostgreSQL extension)
SELECT tablename FROM pg_tables WHERE tablename ILIKE 'PG_Class' LIMIT 1;

-- Multiple conditions
SELECT tablename
FROM pg_tables
WHERE schemaname = 'pg_catalog'
  AND tablename LIKE 'pg_%'
  AND tablename NOT LIKE 'pg_stat_%'
ORDER BY tablename
LIMIT 5;

-- IN: membership test
SELECT tablename
FROM pg_tables
WHERE tablename IN ('pg_class', 'pg_database', 'pg_namespace')
ORDER BY tablename;

-- BETWEEN: inclusive range
SELECT generate_series(1, 10) AS n WHERE generate_series BETWEEN 3 AND 7;

LIKE is the SQL standard pattern operator: % matches any string of characters (including empty), _ matches exactly one character. ILIKE is a PostgreSQL extension that ignores case — handy, but slower than LIKE because it cannot use a regular B-tree index. For more powerful matching, PostgreSQL also supports ~ (regex) and ~* (case-insensitive regex), as well as full-text search (covered later).

AND binds tighter than OR, so A AND B OR C means (A AND B) OR C. When in doubt, add parentheses — your future self reading the query will thank you. IN (...) is shorthand for = ANY (ARRAY[...]) and is equivalent to a chain of ORs.

To run:

$ psql -f source/where.sql postgres
 schemaname |  tablename
------------+--------------
 pg_catalog | pg_aggregate
 pg_catalog | pg_am
 pg_catalog | pg_amop
(3 rows)
 ...

Common pitfalls:

• = NULL and <> NULL are never true (they are NULL). Use IS NULL and IS NOT NULL. This trips up engineers from every other language.
• NOT IN (subquery) becomes mysterious if the subquery contains a NULL — the entire NOT IN evaluates to NULL (i.e. false) for every row. Prefer NOT EXISTS for nullable subqueries.
• LIKE 'abc' does not match 'ABC'. Use ILIKE or apply LOWER() to both sides — but LOWER(col) = 'abc' defeats a plain index on col (add an expression index on LOWER(col) if you need it often).
• Be careful with IN ('a', 'b', NULL) — the NULL in the list contributes nothing useful and silently makes NOT IN behave unexpectedly.

Tip: When filtering text by prefix, WHERE name LIKE 'foo%' can use a B-tree index. WHERE name LIKE '%foo' (suffix) or '%foo%' (substring) cannot — consider a trigram index (pg_trgm extension) for those.

Try it: Change the condition to WHERE schemaname = 'public' — if you have no tables in public yet, you will get zero rows. Then try WHERE tablename ~ '^pg_a.*' to see regex matching.

Source: where.sql

Next: ORDER BY

Home: Postgres by Example