Is Postgres always case-sensitive and MySQL always not?

Postgres string comparison (= and LIKE) is case-sensitive by default, full stop, unless you use a case-insensitive collation explicitly. MySQL's default depends on the table's collation — most common defaults (utf8mb4_general_ci, utf8mb4_0900_ai_ci) are case-insensitive, but a column created with a _cs (case-sensitive) or binary collation won't be. SQL Server is the same story: depends on the database/column collation, which is often case-insensitive by default on many installs but not guaranteed.

Why would the AI not already know this?

describe_table shows column names and types, not collation settings — that's a level deeper than what a schema-inspection tool call typically surfaces. Without being told or checking specifically, there's no reason for the model to assume anything other than 'the filter I wrote should just work.'

What's the actual fix?

In Postgres, use ILIKE instead of LIKE for pattern matching, or wrap both sides in LOWER() for exact-match comparisons: WHERE LOWER(status) = LOWER('active'). MySQL and SQL Server mostly don't need this if the collation is already case-insensitive, but wrapping in LOWER() on both sides is a safe default that works everywhere regardless of collation.

Does this affect JOIN conditions too, not just WHERE?

Yes — if you're joining on a text column (an email, a slug, a code) and one side has different casing than the other, the join can silently produce fewer matches than expected, for exactly the same underlying reason as a WHERE filter missing rows.

Is there a performance cost to LOWER() on both sides?

It can prevent the database from using a normal index on that column, since it's now comparing the output of a function rather than the raw column. For a one-off chat question this rarely matters; for a query that runs often, a case-insensitive collation or a functional index on LOWER(column) is the better long-term fix.

Case Sensitive SQL Search Misses Rows | Postgres vs MySQL

You ask for "all customers with status Active." Some rows with active (lowercase) don't show up. Nothing errored. The query looks completely reasonable. And whether this happens to you at all depends entirely on which database you're connected to — the same filter, word for word, behaves differently across Postgres, MySQL, and SQL Server.

Same SQL, three different outcomes

WHERE status = 'Active' is unambiguous SQL — but "does 'Active' match 'active'" isn't decided by the SQL itself, it's decided by the collation of the column being compared, and collation defaults vary by database:

PostgreSQL — string comparison with = or LIKE is case-sensitive by default. 'Active' = 'active' is false unless you explicitly use a case-insensitive collation.
MySQL — most common default collations (utf8mb4_general_ci, utf8mb4_0900_ai_ci — that ci suffix means case-insensitive) treat 'Active' and 'active' as equal. A column created with a _cs or binary collation is the exception, and behaves like Postgres.
SQL Server — depends on the database or column collation, which on many default installs is case-insensitive, but is a setting, not a guarantee — the same query can behave either way depending on how that specific database was set up.

None of this is visible from the query text. WHERE status = 'Active' reads identically whether it's about to match case-insensitively or not — the actual behavior lives in a collation setting that describe_table doesn't typically surface alongside column names and types.

Why this is easy to miss entirely

If your data happens to be consistently capitalized — every row genuinely says Active, never active — this never shows up, on any database. The bug only appears once the data itself has mixed casing, which tends to happen gradually: an import job, a manual edit, an API that didn't normalize input, a second developer who typed lowercase by habit. At that point, a query that "always worked" starts quietly excluding a growing slice of rows, with the missing rows the only symptom — no error, no warning, just a total that's lower than reality.

How to catch it

If a count or filtered list looks lower than expected and the filter is a text comparison, check for casing variation directly:

SELECT DISTINCT status FROM customers;

If that returns both Active and active as separate values, you've found it — and it's worth deciding whether that variation is itself a data-quality problem worth fixing at the source, separate from the query.

The fix that works everywhere, regardless of collation

Postgres has a dedicated case-insensitive operator for pattern matching:

WHERE status ILIKE 'active'

For exact-match comparisons (not just pattern matching), wrapping both sides in LOWER() works identically across Postgres, MySQL, and SQL Server, regardless of the underlying collation:

WHERE LOWER(status) = LOWER('active')

This is the safer default to ask for specifically when you don't know (or the AI doesn't know) what collation is in play — it removes the ambiguity instead of relying on a setting neither of you inspected.

The prompt that heads this off

When filtering or joining on text columns, don't assume case sensitivity
either way. Use ILIKE (Postgres) or wrap both sides in LOWER() for exact
matches, unless I've confirmed the column's collation is already
case-insensitive. If a count looks lower than expected, check
SELECT DISTINCT on the filtered column for casing variants before
concluding the data itself is smaller than I think.

The short version

The exact same WHERE status = 'Active' is a case-sensitive filter on Postgres and very possibly a case-insensitive one on MySQL — not because either database is doing anything wrong, but because they made different defaults, and nothing in the query text tells you which one you're getting. When a count looks too low, checking for casing variants is worth doing before checking anything else.

Read-only MCP gateway: mcpserver.design.

Related reading: