PostgreSQL Escape Character Conflict (SQLSTATE 2200B)

PostgreSQL raises ERROR: escape character conflict with SQLSTATE 2200B (condition name escape_character_conflict) when you use the LIKE or SIMILAR TO operator with an ESCAPE clause and the specified escape character is the same as one of the SQL wildcard characters — % (percent) or _ (underscore).

What This Error Means

SQLSTATE 2200B belongs to class 22 — the Data Exception class — which groups errors that occur when a value is invalid or out of range for the context in which it is used. The specific condition escape_character_conflict targets a narrowly defined violation in pattern-matching expressions.

The SQL standard requires that the escape character used in a LIKE pattern be distinct from both wildcard characters (% and _). If you specify ESCAPE '%' or ESCAPE '_', PostgreSQL cannot unambiguously interpret the pattern: it would be impossible to tell whether an occurrence of the character is intended as a literal or as a wildcard. Rather than silently producing wrong results, PostgreSQL raises 2200B at parse or execution time.

After this error is raised, the current statement is aborted. If the query was issued inside an explicit transaction block, the transaction enters an error state and must be rolled back with ROLLBACK before any further statements can execute. Outside a transaction block, only the single failed statement is affected and the connection remains usable.

Common Causes

1. Using % as the escape character. Passing ESCAPE '%' is the most common trigger — often seen when developers try to match literal percent signs without realising % itself is the wildcard.
2. Using _ as the escape character. Same problem as above: _ is the single-character wildcard in LIKE patterns, so it cannot also serve as the escape character.
3. Dynamically constructed LIKE expressions. Application code or an ORM that builds ESCAPE clauses from user input or configuration may accidentally pass a wildcard character as the escape character.
4. Copy-paste from non-PostgreSQL SQL dialects. Some databases handle or silently ignore this conflict; migrating those queries to PostgreSQL exposes the error.

How to Fix escape_character_conflict

1. Choose a non-wildcard escape character. The backslash (\) is the PostgreSQL default and almost always the right choice. Any character that does not appear in your data and is not % or _ also works.

   -- Wrong: % cannot be the escape character
   SELECT * FROM products WHERE name LIKE '50\% off' ESCAPE '%';
   
   -- Correct: use backslash (the default)
   SELECT * FROM products WHERE name LIKE '50\% off' ESCAPE '\';
   
   -- Or rely on the implicit default — no ESCAPE clause needed for backslash
   SELECT * FROM products WHERE name LIKE '50\% off';
   

2. Escape literal % and _ with the default backslash escape. If your goal is to search for a literal percent or underscore, prefix it with \ in the pattern rather than redefining the escape character.

   -- Match rows where description contains a literal underscore
   SELECT * FROM logs WHERE description LIKE '%\_log%';
   
   -- Match rows where label ends with a literal percent sign
   SELECT * FROM metrics WHERE label LIKE '%\%';
   

3. Use $$ dollar-quoting or E'' escape strings when the pattern contains backslashes. This avoids double-escaping and makes patterns easier to read.

   -- E-string: \\ represents a single backslash, \% is a literal percent
   SELECT * FROM prices WHERE tag LIKE E'50\\% off';
   
   -- dollar-quoting is cleaner for complex patterns
   SELECT * FROM prices WHERE tag LIKE $q$50\% off$q$;
   

4. Pick an obscure character as the escape character when backslash is inconvenient. For example, ! or # are common alternatives that never conflict with wildcards.

   SELECT * FROM codes WHERE value LIKE '100!%' ESCAPE '!';
   SELECT * FROM codes WHERE value LIKE 'part#_one' ESCAPE '#';
   

5. In application code, validate the escape character before constructing the query. If the escape character comes from user input or configuration, assert it is neither % nor _ before passing it to the database.

   def safe_escape_char(char: str) -> str:
       if char in ('%', '_'):
           raise ValueError(f"Cannot use '{char}' as LIKE escape character")
       return char
   

Additional Information

• SQLSTATE class 22 (Data Exception) contains many related conditions. Sibling codes you may encounter include 22019 (invalid_escape_character — the escape string is longer than one character or is empty) and 22025 (invalid_escape_sequence — an escape sequence in the pattern is malformed).
• 2200B is defined by the SQL standard (SQL:1999 and later), so any conforming database will reject the same pattern. PostgreSQL has enforced this error at least since version 8.x.
• The SIMILAR TO operator uses the same ESCAPE clause semantics as LIKE; the same conflict rules apply.
• ORMs such as SQLAlchemy, Django ORM, and ActiveRecord typically generate LIKE patterns using the default backslash escape and do not expose ESCAPE clauses to application code, so this error almost always originates from hand-written SQL or raw query strings.
• There are no performance implications beyond the aborted statement; this error occurs during query planning/validation before any table I/O.

Frequently Asked Questions

Why can't I use % as the escape character if I want to match literal percent signs? Because % has a fixed meaning as the zero-or-more-characters wildcard in LIKE patterns. If % were also the escape character, the sequence %% would be ambiguous — PostgreSQL could not tell whether it means "escape a wildcard" or "two consecutive wildcards." The SQL standard prohibits this ambiguity and PostgreSQL enforces it with SQLSTATE 2200B.

What is the default escape character in PostgreSQL's LIKE? The default escape character is the backslash (\). You do not need to write an ESCAPE clause at all when using \ to escape wildcards. To disable escaping entirely (treat backslash as a literal), set standard_conforming_strings = on and use ESCAPE '' (an empty escape string), though an empty escape string raises 22019 — use a non-wildcard character instead.

Does this error affect ILIKE or ~~* operators? Yes. ILIKE (case-insensitive LIKE) uses the same pattern-matching engine and the same ESCAPE clause rules. Using % or _ as the escape character with ILIKE raises the same 2200B error.

How do I match a literal backslash in a LIKE pattern? Use \\ in a standard string literal (or \\\\ in a non-escape-string context), or use dollar-quoting: LIKE $q$path\to\file$q$ does not work as-is because \ is the default escape character — you need LIKE $q$path\\to\\file$q$ to match two literal backslashes, or choose a different escape character with ESCAPE '!' and write the pattern without any special treatment of \.