2200BERRORTier 2 — Caution✅ HIGH confidence

escape character conflict

Category: Data ExceptionVersions: All Postgres versions

What this means

SQLSTATE 2200B is raised when an escape character specified in a LIKE or SIMILAR TO pattern conflicts with the pattern syntax — for example, using the wildcard character itself as the escape character.

Why it happens

1Using a LIKE ESCAPE clause where the escape character is the same as a special pattern character (% or _)

How to reproduce

LIKE with a conflicting escape character.

trigger — this will ERROR

SELECT * FROM items WHERE name LIKE '50%' ESCAPE '%';
-- % cannot be both wildcard and escape

ERROR: invalid escape character

Fix 1: Use a neutral escape character such as backslash

When escaping wildcards in LIKE patterns.

fix

SELECT * FROM items WHERE name LIKE '50\%' ESCAPE '\';

Why this works

Backslash is the conventional escape character and does not conflict with the LIKE wildcards % and _.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 22 — Data Exception

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE. Stable across versions.