PostgreSQL WHERE Clause

The WHERE clause is the primary tool for filtering rows in PostgreSQL. It can appear in SELECT, UPDATE, and DELETE statements and accepts any Boolean expression — from simple equality checks to complex subqueries involving indexes and multi-table lookups.

Syntax

SELECT column_list
FROM table_name
WHERE condition;

The same clause applies to write operations:

UPDATE table_name
SET column = value
WHERE condition;

DELETE FROM table_name
WHERE condition;

PostgreSQL evaluates the logical order as: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. This ordering means that aliases defined in SELECT are not visible to WHERE.

Practical Example

Create a sample schema for an event ticketing system:

CREATE TABLE venues (
  venue_id   SERIAL PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  city       VARCHAR(60)  NOT NULL
);

CREATE TABLE events (
  event_id   SERIAL PRIMARY KEY,
  title      VARCHAR(150) NOT NULL,
  venue_id   INT REFERENCES venues (venue_id),
  starts_at  TIMESTAMPTZ  NOT NULL,
  price_usd  NUMERIC(8,2) NOT NULL,
  status     VARCHAR(20)  DEFAULT 'scheduled'
);

INSERT INTO venues (name, city) VALUES
  ('Grand Hall', 'Berlin'),
  ('Studio 9',   'Berlin'),
  ('Riverside Arena', 'Hamburg');

INSERT INTO events (title, venue_id, starts_at, price_usd, status) VALUES
  ('Opening Night',    1, '2026-06-01 19:00+02', 45.00, 'scheduled'),
  ('Jazz Evening',     1, '2026-06-15 20:00+02', 30.00, 'scheduled'),
  ('Private Showcase', 2, '2026-06-10 18:00+02', 80.00, 'cancelled'),
  ('Summer Concert',   3, '2026-07-04 21:00+02', 55.00, 'scheduled'),
  ('Workshop Day',     2, '2026-06-20 10:00+02', 20.00, 'scheduled');

Filter scheduled events priced above €40:

SELECT title, price_usd, starts_at
FROM events
WHERE status = 'scheduled'
  AND price_usd > 40.00
ORDER BY starts_at;

Expected result:

      title       | price_usd |          starts_at
------------------+-----------+----------------------------
 Opening Night    |     45.00 | 2026-06-01 19:00:00+02
 Summer Concert   |     55.00 | 2026-07-04 21:00:00+02

Verify the cancelled event is excluded:

SELECT COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_count
FROM events;

WHERE with Multiple Condition Types

Comparison and range operators

-- Range filter (BETWEEN is inclusive on both ends)
SELECT title, price_usd
FROM events
WHERE price_usd BETWEEN 20.00 AND 50.00;

-- Date range
SELECT title, starts_at
FROM events
WHERE starts_at >= '2026-06-01'
  AND starts_at  < '2026-07-01';

IN for list membership

SELECT title, status
FROM events
WHERE status IN ('scheduled', 'on_sale');

Pattern matching with LIKE and ILIKE

-- Case-sensitive prefix match
SELECT title FROM events WHERE title LIKE 'Jazz%';

-- Case-insensitive substring match
SELECT title FROM events WHERE title ILIKE '%concert%';

IS NULL check

-- Find events not yet assigned to a venue
SELECT title FROM events WHERE venue_id IS NULL;

Subquery in WHERE

-- Events held at venues in Berlin
SELECT title
FROM events
WHERE venue_id IN (
  SELECT venue_id FROM venues WHERE city = 'Berlin'
);

Testing with Vela

Before running UPDATE or DELETE statements with a WHERE clause in production, use Vela’s database branching to validate the filter. Create a branch from your production database — an instant copy-on-write clone — run the statement as a SELECT first to see exactly which rows would be affected, then promote the change to production only when the result set looks correct. This prevents accidental mass-updates caused by a missing or incorrect WHERE predicate.

Production Tips

• Always wrap UPDATE and DELETE with WHERE in a BEGIN/COMMIT block so you can ROLLBACK if the wrong rows are affected.
• Run a SELECT with the same WHERE clause before executing destructive statements — verify the row count matches expectations.
• Index columns that appear frequently in WHERE for equality or range comparisons. An unindexed filter on a large table causes a full sequential scan.
• Avoid wrapping indexed columns in functions (WHERE lower(email) = 'x'): this breaks index usage. Use a functional index or ILIKE instead.
• NOT IN is hazardous when the subquery can return NULL — any NULL in the list causes NOT IN to return zero rows. Prefer NOT EXISTS.
• Use EXPLAIN ANALYZE to inspect the query plan and confirm that filters are applied efficiently.