PostgreSQL Invalid Use of Escape Character (SQLSTATE 2200C)

PostgreSQL raises ERROR: invalid use of escape character with SQLSTATE 2200C (condition name invalid_use_of_escape_character) when an escape character in a LIKE or SIMILAR TO pattern is used in a syntactically invalid way — most commonly, when the escape character appears as the very last character in the pattern with no following character to escape.

What This Error Means

SQLSTATE 2200C belongs to error class 22 — Data Exception — which covers a broad family of errors related to invalid data values or operations on data. This particular condition is specific to pattern-matching operations that accept an ESCAPE clause.

In PostgreSQL's LIKE and SIMILAR TO operators, the escape character (default \) is used to treat a wildcard character (% or _) as a literal. When PostgreSQL encounters the escape character in a pattern, it expects exactly one character to follow it. If the escape character is the last character in the string — or is otherwise positioned where it cannot precede another character — PostgreSQL cannot complete the pattern interpretation and raises this error.

The error is raised at query execution time, not at parse time. The transaction remains open after this error in most cases, but the current statement is rolled back. If the query was issued inside an explicit transaction block, the transaction enters an error state and must be rolled back before new work can proceed.

Common Causes

1. Escape character at the end of a LIKE pattern. The most frequent cause. A pattern like 'abc\' ends with the escape character, leaving nothing for it to escape.

2. Dynamically built LIKE patterns with improper escaping. Application code that constructs a LIKE pattern by appending a user-supplied string without sanitizing trailing backslashes (or custom escape characters) will produce this error when the input ends with a backslash.

3. Misuse of a custom ESCAPE clause. Using LIKE '50%' ESCAPE '%' with % as the escape character, and then ending the pattern with % and nothing after it, produces the same error because % now means "escape the next character" and there is none.

4. Copy-paste of patterns from other contexts. Patterns copied from regular expression syntax or shell glob syntax may include trailing escape sequences that are valid elsewhere but illegal in SQL LIKE patterns.

How to Fix invalid_use_of_escape_character

1. Remove or complete the trailing escape sequence. Ensure the escape character in a LIKE pattern is always followed by exactly one character.

   -- Raises 2200C: escape character is the last character
   SELECT * FROM products WHERE name LIKE 'widget\';
   
   -- Fixed: either remove the trailing backslash or escape it
   SELECT * FROM products WHERE name LIKE 'widget';
   -- or, to match a literal backslash at the end:
   SELECT * FROM products WHERE name LIKE 'widget\\';
   

2. Escape user-supplied input before embedding in LIKE patterns. In application code, replace any backslash (or custom escape character) in the user input before appending wildcards.

   -- In application code (Python example concept):
   -- user_input = user_input.replace('\\', '\\\\')
   -- Then safely build: LIKE '%' || user_input || '%'
   
   -- Alternatively, use the regexp_replace approach in SQL:
   SELECT *
   FROM products
   WHERE name LIKE '%' || regexp_replace(user_input, '\\', '\\\\', 'g') || '%';
   

3. Use a custom ESCAPE character that avoids conflicts. If your data contains many backslashes, choose a different escape character that is unlikely to appear in the data:

   -- Use '!' as the escape character to avoid backslash confusion
   SELECT * FROM products WHERE name LIKE '50!%off' ESCAPE '!';
   

4. Disable the escape character entirely with ESCAPE ''. If you do not need to match literal % or _ characters in the pattern, you can disable the escape mechanism completely:

   -- No escape character; % and _ are always wildcards
   SELECT * FROM products WHERE name LIKE '%widget%' ESCAPE '';
   

Additional Information

• SQLSTATE class 22 (Data Exception) contains many related pattern-matching errors. Sibling conditions include 2201B (invalid_regex_error) for invalid regular expression syntax, and 2200D (invalid_escape_octet) for similar issues in binary string functions.
• The standard_conforming_strings setting (on by default since PostgreSQL 9.1) affects how backslash literals are interpreted in string constants. With standard_conforming_strings = on, a single \ in a string literal is just a backslash; to write a backslash in the pattern you write \\. With it off (legacy mode), \\ in the source becomes \ in the string value. This can cause double-escaping confusion when building dynamic patterns.
• Most ORM frameworks that generate LIKE queries (Django ORM, Hibernate, ActiveRecord) escape user input automatically, but raw or hand-written query strings bypass this protection.
• This error does not indicate data corruption and has no persistent effect on the database. The failing statement is simply rejected.

Frequently Asked Questions

Why does this error only appear with certain user inputs? The error is triggered only when the pattern string contains an improperly positioned escape character. Static patterns in source code are usually correct; the error surfaces when user-supplied text is interpolated into a LIKE pattern without sanitization — for example, a search term that ends with a backslash.

Does this affect ILIKE as well as LIKE? Yes. PostgreSQL's ILIKE (case-insensitive LIKE) uses the same pattern-matching engine and the same ESCAPE clause semantics. The same error will be raised if the escape character is misused in an ILIKE pattern.

Can I just catch this error in my application and retry? Retrying with the same input will produce the same error. The fix must be in how the pattern is constructed. Catching the error is useful for returning a user-friendly message, but the underlying pattern-building logic needs to be corrected.

Is there a PostgreSQL function to safely escape LIKE patterns? PostgreSQL does not have a built-in like_escape() function, but you can use regexp_replace to escape all special characters before embedding user input in a pattern: regexp_replace(input, '([%_\\])', '\\\1', 'g'). Some client libraries (e.g., psycopg2's psycopg2.extensions.adapt) provide helpers for this purpose.