invalid escape character

Category: Data ExceptionVersions: All Postgres versions

What this means

SQLSTATE 22019 is raised when an ESCAPE clause in a LIKE or SIMILAR TO expression specifies a multi-character string or an empty string, which is not a valid escape character specification.

Why it happens

1LIKE ESCAPE specifying a string with more than one character
2LIKE ESCAPE specifying an empty string

How to reproduce

LIKE with a multi-character escape string.

trigger — this will ERROR

SELECT * FROM items WHERE name LIKE '50!%' ESCAPE '!!'; -- two chars

ERROR: invalid escape string HINT: Escape string must be empty or one character.

Fix 1: Use exactly one character as the escape character

When using LIKE ESCAPE.

fix

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

Why this works

The ESCAPE clause requires exactly one character. Choose a character that does not appear in normal data.

Sources

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

🔧 Source ref: Class 22 — Data Exception

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for LIKE escape character validation. Stable across versions.