saascode
saascode

PostgreSQL LIKE treats underscore as a wildcard

In PostgreSQL LIKE, underscore matches any one character. Learn how an innocent prefix filter can exclude every row and how to escape it safely.

academy · aug 21, 2026 · 2 min read

You write NOT LIKE '__%' to exclude names beginning with two underscores. The query runs, returns no error, and quietly excludes almost every name with at least two characters.

That happened in a schema-introspection query. It filtered all 49 application tables, and the fallout took 8 migrations to unwind.

The bug is standard SQL behavior: inside a LIKE pattern, _ matches exactly one character and % matches any sequence. So __% means “any two characters, followed by anything.” It does not mean a literal double-underscore prefix.

Pick an escape character you can read

PostgreSQL lets you declare the escape character explicitly. Using ! avoids the layers of backslash escaping that appear when SQL is embedded in another language:

select table_name
from information_schema.tables
where table_name not like '!_!_%' escape '!';

Here, each !_ means a literal underscore. The final % remains a wildcard, so the pattern now means “starts with __.”

You can use a backslash instead, but make the ESCAPE clause explicit when clarity matters. The exact representation may otherwise become hard to review across SQL, JavaScript strings, migration tooling, and shell quoting.

Parameters do not remove pattern semantics

A parameterized query protects the SQL grammar, but the bound value is still interpreted as a LIKE pattern. If a user searches for 50%_off, the percent sign and underscore remain wildcards unless your product intends them to be.

Escape the chosen escape character first, then % and _:

function escapeLike(value: string) {
  return value
    .replaceAll('!', '!!')
    .replaceAll('%', '!%')
    .replaceAll('_', '!_')
}

The SQL using that value must declare ESCAPE '!'. Keep the escaping helper and the SQL convention together; changing one without the other creates a new bug.

Review every identifier pattern

Snake case makes this especially easy to miss. A pattern such as user_% may mean “the literal prefix user_,” or it may intentionally accept any character after user. A reviewer cannot tell unless the underscore is escaped or the intent is documented.

Search migrations and query builders for LIKE and ILIKE. For every % and _, ask whether it is data or syntax. Then test a positive case, a near miss, and a short string. The original query passed code review because it looked like English. The database read it as a pattern language.

end