Filtering rows in PostgreSQL.

How to filter PostgreSQL rows correctly

1. 1

   Case-insensitive text: ILIKE

   LIKE is case-sensitive. ILIKE is PostgreSQL's case-insensitive version and is almost always what you want when searching names or emails typed by a human. For whole-value comparison, lower() on both sides works too, but it needs an expression index to stay fast.

   SELECT * FROM users WHERE email ILIKE '%@example.com';
   
   -- Equivalent for exact matching, needs an expression index to be fast:
   SELECT * FROM users WHERE lower(email) = lower('Someone@Example.com');

2. 2

   NULL needs IS NULL

   NULL means unknown, so any comparison with it returns unknown rather than true — including = NULL and even NULL = NULL. This is the single most common filtering mistake in SQL. Use IS NULL and IS NOT NULL, and remember that NOT IN with a list containing a NULL returns no rows at all.

   SELECT count(*) FROM users WHERE deleted_at IS NULL;
   
   -- Returns nothing if the subquery yields any NULL:
   SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM bans);
   -- Safe version:
   SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM bans b WHERE b.user_id = u.id);

3. 3

   Timestamp ranges: half-open, not BETWEEN

   BETWEEN is inclusive at both ends, so a range ending at a date drops every row after midnight on that day. With timestamps, always use >= the start and < the start of the next period. This bug is invisible until someone notices a report is missing a day.

   -- Misses everything after 2026-01-31 00:00:00
   SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';
   
   -- Correct
   SELECT * FROM orders
   WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

4. 4

   JSONB and arrays

   ->> extracts a field as text, and @> tests containment. Containment is the one that can use a GIN index, so prefer it for filters on jsonb columns. For arrays, = ANY is the readable way to test membership.

   -- Field equality
   SELECT * FROM events WHERE payload->>'type' = 'signup';
   
   -- Containment, GIN-indexable
   SELECT * FROM events WHERE payload @> '{"type": "signup"}';
   
   -- Array membership
   SELECT * FROM posts WHERE 'postgres' = ANY(tags);

5. 5

   Check whether the filter uses an index

   EXPLAIN ANALYZE tells you what actually happened. A sequential scan on a small table is fine; on a large one it means your filter could not use the index. A leading wildcard is the usual cause — 'foo%' can use a B-tree, '%foo' cannot, and that needs a trigram index.

   EXPLAIN ANALYZE SELECT * FROM users WHERE email ILIKE '%@example.com';
   
   -- Makes leading-wildcard and ILIKE searches indexable:
   CREATE EXTENSION IF NOT EXISTS pg_trgm;
   CREATE INDEX users_email_trgm ON users USING gin (email gin_trgm_ops);

6. 6

   Or filter visually

   For exploration, a GUI client's filters are faster than writing predicates. PgDeck's filters are type-aware — a boolean column offers true and false, a timestamp offers a date input, and global search uses ILIKE — so the NULL and case-sensitivity traps above are handled for you.