PostgreSQL Where

Filtering Rows with WHERE

WHERE sits between FROM and any grouping or ordering clauses, and it's evaluated for every row before that row is included in the result. Only rows where the condition evaluates to true make it through; rows evaluating to false or unknown are dropped.

SELECT name, department, salary
FROM employees
WHERE department = 'Engineering';

Range and Set Filtering

Beyond simple comparisons, PostgreSQL provides BETWEEN for inclusive ranges and IN for matching against a list of candidate values. Both are shorthand for combinations of comparison and logical operators, but they read more clearly and often perform just as well.

SELECT name, salary, department
FROM employees
WHERE salary BETWEEN 50000 AND 90000
  AND department IN ('Engineering', 'Product', 'Design');

Pattern Matching and NULLs

LIKE matches text against a pattern using two wildcards, and IS NULL checks for the absence of a value. PostgreSQL also adds ILIKE, a case-insensitive version of LIKE that isn't part of the SQL standard but is available on every PostgreSQL installation.

• % in a LIKE pattern matches any sequence of characters, including none
• _ in a LIKE pattern matches exactly one character
• LIKE is case-sensitive; ILIKE is PostgreSQL's case-insensitive equivalent
• IS NULL and IS NOT NULL are the only correct ways to test for NULL

SELECT name, email
FROM employees
WHERE email ILIKE '%@hyring.com'
  AND manager_id IS NOT NULL;