← Back to TIL

underscore is a wildcard in postgresql `LIKE`

Jan 12, 2026

postgresqlsql

in sql, the LIKE operator has two wildcards, not one:

  • % matches any number of characters
  • _ matches exactly one character

this means _ is reserved, not literal.

example pitfall (actually my own gotcha lol):

col NOT LIKE '%_'

this does not mean “string does not contain underscore in the tail of the string”.

instead, it means “string does not end with at least one character”, which is basically false for empty strings only. result: almost all non-empty rows pass.

to match a literal underscore, you must escape it:

col NOT LIKE '%\_' ESCAPE '\'

or, more sanely, use postgres regex operators:

col !~ '_'

or to be explicit about position:

col !~ '_$'

reference: