Where Clause In PostgreSQL

WHERE is one of the first things you learn in SQL, and it's also one of the things people stop paying attention to too early. It feels solved — you know the syntax, you've written a hundred of these, why would there be more to say about it. But there are a few Postgres-specific details in here that genuinely surprised me, and a habit around combining conditions that I only picked up after seeing it go wrong once.

Where this quietly causes bugs

Say you've got a query filtering venues that are open, and either in Bhopal or have no city set yet:

SELECT * FROM venues
WHERE is_closed = FALSE
  AND city = 'Bhopal' OR city IS NULL;

This looks reasonable at a glance. It's wrong. Without parentheses, AND binds tighter than OR, so this actually reads as "give me rows where (closed is false and city is Bhopal), or (city is null) — regardless of whether they're closed." You'd get back closed venues with no city set, which almost certainly isn't what you meant.

This is the kind of bug that doesn't announce itself. The query runs, it returns rows, nothing errors — it's just quietly including data it shouldn't. You usually catch it because a number looks off in a report, not because anything crashed.

The basics, and the Postgres-specific one

SELECT * FROM venues WHERE latitude > 23.2;
SELECT * FROM venues WHERE name IN ('DB Mall', 'Upper Lake');
SELECT * FROM venues WHERE name LIKE 'Van%';        -- case-sensitive pattern match
SELECT * FROM venues WHERE name ILIKE 'van%';       -- case-insensitive
SELECT * FROM venues WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31';
SELECT * FROM venues WHERE city IS NULL;

Most of this is standard SQL and probably familiar already. ILIKE is the one worth calling out specifically, because it's Postgres-specific — MySQL's LIKE happens to behave case-insensitively by default depending on the collation setting, which is its own small gotcha if you're used to that behavior and then move to Postgres and suddenly your pattern matches stop working the way you expect. In Postgres, LIKE is case-sensitive, full stop, and ILIKE is the explicit case-insensitive version. If someone asks about this in an interview, it's usually less about the syntax and more about whether you've actually worked with Postgres specifically rather than SQL in the abstract.

IN versus a chain of OR conditions is mostly a readability choice at this scale — WHERE name IN ('DB Mall', 'Upper Lake') says the same thing as WHERE name = 'DB Mall' OR name = 'Upper Lake', just more compactly once you're checking against more than two or three values.

Combining conditions without shooting yourself

SELECT * FROM venues
WHERE is_closed = FALSE
  AND (city = 'Bhopal' OR city IS NULL);

The fix for the earlier bug is just parentheses, grouping the OR explicitly. The bigger point isn't the specific bug — it's that relying on memorized operator precedence rules, and getting them right every time under pressure, isn't a great strategy. Parenthesizing mixed AND/OR conditions costs nothing and means the query reads correctly on its own, without anyone needing to recall precedence rules to verify it during a code review.

I started doing this by default after the bug above, not because someone told me to as a rule. It's one of those habits that feels unnecessary right up until it isn't.

Sorting and limiting

SELECT * FROM venues ORDER BY created_at DESC LIMIT 5;

Not much complexity here on its own — ORDER BY sorts, LIMIT caps how many rows come back. The thing worth knowing now, even though it's really a topic for the indexing article later in this series, is that ORDER BY on a large table without a supporting index means Postgres has to sort the entire result set before it can hand back even a LIMIT 5. It works fine on a few thousand rows. It gets noticeably slower as a table grows, which is part of why indexing decisions and query patterns end up tied together more than they look at first.

Putting a few of these together

Say you're building the "nearby open venues in my city, or venues where we don't know the city yet" view for the dashboard — a genuinely realistic filter, since city data synced from an external API is exactly the kind of field that sometimes comes back empty:

SELECT name, latitude, longitude, city
FROM venues
WHERE is_closed = FALSE
  AND (city ILIKE 'bhopal' OR city IS NULL)
ORDER BY name ASC
LIMIT 20;

This uses ILIKE instead of = for the city comparison, since city names synced from an external source aren't always consistently capitalized — Bhopal versus bhopal versus BHOPAL depending on which upstream feed the data came from. The parentheses around the OR keep the null-handling correct. And LIMIT caps it to a reasonable page size instead of pulling back everything at once. None of these individually is complicated, but stacking them correctly, in the right order and with the right grouping, is really what filtering well in practice comes down to.

Where this leaves you

WHERE clauses feel basic enough that it's easy to stop thinking carefully about them once you know the syntax. The two things actually worth carrying forward: parenthesize mixed AND/OR conditions every time, even when you're fairly sure you have precedence right, and remember that ILIKE is a Postgres-specific tool, not something you can assume exists everywhere.

Next up: joins — which is where a lot of the real anxiety around SQL interviews tends to live, and honestly, for good reason, since it's easy to get subtly wrong in a way that still returns results.