PostgreSQL Invalid Escape Character (SQLSTATE 22019)

PostgreSQL raises ERROR: invalid escape character with SQLSTATE 22019 and condition name invalid_escape_character when the argument provided to the ESCAPE clause of a LIKE or ILIKE expression is not exactly one character. The full error message typically reads:

ERROR:  invalid escape character
SQLSTATE: 22019

What This Error Means

SQLSTATE 22019 belongs to error class 22 — Data Exception — which covers a broad range of errors related to invalid data values encountered during SQL operations. Other members of this class include 22001 (string data right truncation), 22003 (numeric value out of range), and 22007 (invalid datetime format).

PostgreSQL's LIKE and ILIKE operators support an optional ESCAPE clause that lets you specify a single character to use as the escape character within the pattern. This is necessary when you need to match a literal % or _ — the two wildcard characters in LIKE patterns. The SQL standard and PostgreSQL both require that this escape character be exactly one character long.

When the string passed to ESCAPE is empty ('') or contains more than one character (e.g., '\\' as a two-character string), PostgreSQL raises 22019. The error is thrown at query execution time, not during planning, so it will appear when the offending query actually runs — including inside functions, triggers, or application code that constructs dynamic SQL.

After this error occurs the current statement is aborted. If you are inside an explicit transaction, the transaction enters an error state and must be rolled back before any further commands can execute.

Common Causes

1. Empty string passed as the escape character. Passing '' (zero characters) is invalid. Some developers mistakenly use an empty string to "disable" escaping, but PostgreSQL does not support this syntax.

2. Multi-character string passed as the escape character. Passing a string like '\\' (two characters: a backslash and another backslash) or any other multi-character value violates the single-character requirement.

3. Dynamically constructed ESCAPE argument with a bug. Application code that builds the escape character programmatically (e.g., from a config value or user input) may produce a zero-length or multi-character string under certain conditions.

4. Driver or ORM parameter binding producing an unexpected value. Some ORMs or query builders abstract LIKE pattern handling and may pass an incorrect escape value depending on their version or configuration.

How to Fix invalid_escape_character

1. Use exactly one character in the ESCAPE clause. The most common convention is a backslash or an exclamation mark:

   -- Correct: single-character escape
   SELECT * FROM products WHERE name LIKE '%50\% off%' ESCAPE '\';
   
   -- Also correct: use ! as the escape character
   SELECT * FROM products WHERE name LIKE '%50!% off%' ESCAPE '!';
   

2. Remove the ESCAPE clause if you do not need to match literal wildcards. If your pattern does not contain % or _ characters that need to be treated literally, drop the ESCAPE clause entirely:

   -- No ESCAPE needed when wildcards are used normally
   SELECT * FROM users WHERE username LIKE 'john%';
   

3. Guard dynamic escape values in application code. If the escape character comes from a variable, validate that it is exactly one character before building the query:

   # Python example
   escape_char = config.get("escape_char", "\\")
   if len(escape_char) != 1:
       raise ValueError("escape_char must be exactly one character")
   

4. To disable escape processing entirely, use a workaround. PostgreSQL does not allow an empty escape string, but you can avoid needing one by using the ~~ operator variant or by ensuring your pattern contains no characters that would be misinterpreted. Alternatively, use SIMILAR TO or a regular expression (~) operator when complex pattern matching is required:

   -- Use regex instead of LIKE when escape handling is complex
   SELECT * FROM logs WHERE message ~ '50% discount';
   

Additional Information

• SQLSTATE class 22 (Data Exception) contains many related pattern-matching errors. A closely related code is 22025 (invalid_escape_sequence), which is raised when a valid single-character escape is provided but an invalid escape sequence appears within the pattern itself (e.g., \z when \ is the escape character and z is not % or _).
• This error has been consistent across all modern PostgreSQL versions (9.x through 17.x). The behavior is mandated by the SQL standard.
• PostgreSQL's default escape character for LIKE is the backslash (\) when no ESCAPE clause is specified. This default can be changed at the session or database level via the standard_conforming_strings and escape_string_warning settings, but the requirement that an explicit ESCAPE value be a single character is unconditional.
• JDBC drivers surface this as a PSQLException with SQLState 22019. Most ORMs (Hibernate, SQLAlchemy, ActiveRecord) propagate the underlying database error message with the SQLSTATE intact.

Frequently Asked Questions

Why can't I use an empty string to disable escaping in LIKE? The SQL standard requires the ESCAPE argument to be a character, not an absence of one. To write a LIKE pattern that matches % or _ literally without an escape character, you can switch to a different operator such as SIMILAR TO (which has its own escape rules) or a POSIX regular expression using ~, where those characters have no special meaning unless you add quantifiers.

What is the difference between SQLSTATE 22019 and 22025? 22019 (invalid_escape_character) means the value given to the ESCAPE clause is not a valid single character — the escape character itself is the problem. 22025 (invalid_escape_sequence) means the escape character is valid, but it appears in the pattern followed by a character other than %, _, or the escape character itself, forming an illegal sequence.

Does this error abort my entire transaction? Yes. In PostgreSQL, any error aborts the current statement and places an open transaction into an error state. No further SQL commands (other than ROLLBACK or ROLLBACK TO SAVEPOINT) will execute until the transaction is rolled back. Use savepoints inside long transactions if you need to recover from this error without rolling back all prior work.

Can I catch this error in PL/pgSQL? Yes. Use an EXCEPTION block and catch invalid_escape_character by condition name or by SQLSTATE:

BEGIN
  PERFORM * FROM t WHERE col LIKE pattern ESCAPE esc_char;
EXCEPTION
  WHEN invalid_escape_character THEN
    RAISE NOTICE 'Invalid escape character provided: %', esc_char;
END;