PostgreSQL 2200B Error: Causes and Solutions Complete Guide

PostgreSQL Error 2200B: escape character conflict

PostgreSQL error code 2200B (escape character conflict) occurs when an invalid escape character is specified in the ESCAPE clause of a LIKE or SIMILAR TO expression. Specifically, this error is raised when you attempt to use a wildcard character (% or _) as the escape character, or when the escape string contains more than one character. This error most commonly surfaces in applications that dynamically generate SQL queries with user-supplied input.

---

Top 3 Causes

1. Using a wildcard character (% or _) as the escape character

PostgreSQL reserves % and _ as special pattern-matching wildcards inside LIKE expressions. Attempting to reuse either of them as the escape character creates a logical conflict.

-- ERROR: using % as escape character
SELECT * FROM products
WHERE product_code LIKE '50%OFF' ESCAPE '%';
-- ERROR:  2200B escape character conflict

-- ERROR: using _ as escape character
SELECT * FROM employees
WHERE emp_code LIKE 'EMP_001' ESCAPE '_';
-- ERROR:  2200B escape character conflict

-- CORRECT: use a neutral single character instead
SELECT * FROM products
WHERE product_code LIKE '50!%OFF' ESCAPE '!';

2. Specifying a multi-character escape string

The SQL standard and PostgreSQL require the escape string to be exactly one character (or an empty string to disable escaping). Passing two or more characters—such as ##, \\, or !!—immediately triggers error 2200B.

-- ERROR: two-character escape string
SELECT * FROM orders
WHERE ref_code LIKE 'ORD##001' ESCAPE '##';
-- ERROR:  2200B escape character conflict

-- ERROR: double backslash
SELECT * FROM orders
WHERE ref_code LIKE 'ORD\\001' ESCAPE '\\';
-- ERROR:  2200B escape character conflict

-- CORRECT: single backslash
SELECT * FROM orders
WHERE ref_code LIKE 'ORD\001' ESCAPE '\';

3. Improperly handled escape characters from application-layer dynamic SQL

ORMs and query builders that interpolate user input into LIKE patterns can accidentally produce multi-character or reserved escape strings at runtime—especially when double-escaping logic misfires.

-- Simulating a bug where the app sends '!!' as the escape char
SELECT * FROM customers
WHERE email LIKE '[e-mail]' ESCAPE '!!';
-- ERROR:  2200B escape character conflict

-- Safe pattern: sanitize input with a helper function first
CREATE OR REPLACE FUNCTION escape_like(input TEXT, esc TEXT DEFAULT '!')
RETURNS TEXT LANGUAGE sql IMMUTABLE STRICT AS $$
    SELECT replace(replace(replace(input, esc, esc||esc), '%', esc||'%'), '_', esc||'_');
$$;

-- Use the helper in queries
SELECT * FROM customers
WHERE email LIKE '%' || escape_like('[e-mail]') || '%' ESCAPE '!';

---

Quick Fix Solutions

-- Fix 1: Replace wildcard escape char with a safe character
-- Before (broken)
SELECT * FROM t WHERE col LIKE 'data%' ESCAPE '%';

-- After (fixed)
SELECT * FROM t WHERE col LIKE 'data!%' ESCAPE '!';

-- Fix 2: Reduce multi-char escape string to a single character
-- Before (broken)
SELECT * FROM t WHERE col LIKE 'val##key' ESCAPE '##';

-- After (fixed)
SELECT * FROM t WHERE col LIKE 'val#key' ESCAPE '#';

-- Fix 3: Disable escaping entirely when not needed
SELECT * FROM t WHERE col LIKE 'simple_pattern' ESCAPE '';

-- Fix 4: Use the escape_like() helper for dynamic input
SELECT * FROM products
WHERE name LIKE '%' || escape_like(:user_input) || '%' ESCAPE '!';

---

Prevention Tips

Standardize on a single escape character across your codebase.
Pick one safe character—! is a popular choice—and enforce it via a shared database function (escape_like()). Document this convention in your team's SQL style guide so no one reaches for %, _, or multi-character strings.

-- Team-standard safe search query pattern
SELECT *
FROM product_catalog
WHERE product_name LIKE '%' || escape_like(:search_term, '!') || '%'
ESCAPE '!';

Add static analysis or integration tests to catch bad ESCAPE clauses before deployment.
Use tools like sqlfluff or pgTAP to flag any LIKE ... ESCAPE expressions that use more than one character or use reserved wildcards as the escape character. Catching these in CI is far cheaper than debugging a production outage.

-- pgTAP sanity check for the escape_like helper
SELECT plan(2);
SELECT is(escape_like('100% done'), '100!% done', '% should be escaped');
SELECT is(escape_like('file_name'), 'file!_name', '_ should be escaped');
SELECT * FROM finish();

Related errors: 22025 (invalid escape sequence), 22019 (invalid escape character), 2201B (invalid regular expression).

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.