PostgreSQL 2200D Error: Causes and Solutions Complete Guide

PostgreSQL Error 2200D: Invalid Escape Octet

PostgreSQL error 2200D: invalid escape octet occurs when the database engine encounters an invalid escape sequence within a byte string (bytea) value or a pattern-matching expression. This typically happens when an application sends raw binary data using an incorrect escape format, or when a LIKE / SIMILAR TO query specifies an invalid escape character. Understanding the root causes helps you resolve this quickly and prevent it from recurring in production.

---

Top 3 Causes and Fixes

1. Invalid Escape Sequences in bytea Literals

When using the legacy escape format for bytea, only valid octal sequences (\NNN) and \\ are accepted. Using hex-style notation (\x) inside an escape-mode string will trigger this error.

-- BAD: mixing hex notation in escape-mode bytea
SELECT E'\\xFF'::bytea;  -- raises 2200D

-- GOOD: use the hex format (recommended since PostgreSQL 9.0)
SELECT '\xFF'::bytea;

-- GOOD: valid octal escape in traditional format
SELECT E'\\377'::bytea;  -- octal 377 = decimal 255

-- GOOD: use encode/decode for safe binary handling
SELECT decode('DEADBEEF', 'hex');
SELECT encode('\xDEADBEEF'::bytea, 'base64');

-- Set hex as default at the database level
ALTER DATABASE mydb SET bytea_output = 'hex';

2. Invalid Escape Character in LIKE / SIMILAR TO

PostgreSQL requires exactly one valid single-byte character as the escape argument in LIKE ... ESCAPE. Passing a multi-byte character or an invalid octet raises 2200D.

-- BAD: multi-byte character as escape (raises 2200D)
-- SELECT * FROM t WHERE col LIKE '50!%' ESCAPE '€';

-- GOOD: use a plain ASCII character as the escape
SELECT * FROM orders
WHERE description LIKE '%50!%%' ESCAPE '!';

-- GOOD: default backslash escape (no ESCAPE clause needed)
SELECT * FROM orders
WHERE description LIKE '%100\%%';

-- GOOD: avoid LIKE altogether for simple substring checks
SELECT * FROM orders
WHERE POSITION('%' IN description) > 0;

-- Safe helper function to escape user input
CREATE OR REPLACE FUNCTION escape_like(p_text text)
RETURNS text LANGUAGE sql IMMUTABLE STRICT AS $$
  SELECT replace(replace(replace(p_text, '\', '\\'), '%', '\%'), '_', '\_');
$$;

SELECT * FROM products
WHERE name LIKE '%' || escape_like('sale 50%') || '%';

3. Client/Server Encoding Mismatch

When the client sends data in one encoding (e.g., Latin-1) but PostgreSQL interprets it as another (e.g., UTF-8), certain byte combinations can be parsed as invalid escape octets.

-- Check current encoding settings
SHOW server_encoding;
SHOW client_encoding;

-- Fix: align client encoding with the server
SET client_encoding = 'UTF8';

-- Verify database encoding
SELECT datname, pg_encoding_to_char(encoding)
FROM pg_database
WHERE datname = current_database();

-- Safely convert legacy encoded data
SELECT convert_from(
    lo_get(lo_import('/path/to/legacy_file')),
    'LATIN1'
);

-- Re-encode problematic bytea data
SELECT encode(
    convert_to(legacy_text_col, 'UTF8'),
    'hex'
)
FROM legacy_table;

---

Quick Fix Checklist

Scenario	Fix
bytea insertion fails	Switch to '\xNN' hex format
LIKE ESCAPE error	Use a single ASCII char (e.g., !)
Encoding mismatch	SET client_encoding = 'UTF8'
Legacy data migration	Use convert_from() / convert_to()

---

Prevention Tips

Always use hex format and parameterized queries.
Set bytea_output = 'hex' at the database level and rely on your driver's binary binding (e.g., psycopg2.Binary(), JDBC setBytes()) instead of building escape strings manually. This eliminates the entire class of bytea escape errors.

Validate and sanitize pattern inputs before passing to LIKE.
Use the escape_like() helper function shown above or delegate escaping to your ORM. Never concatenate raw user input directly into a LIKE pattern, as this also exposes you to SQL injection risks alongside the 2200D error.

---

Related Errors

• 22025 invalid_escape_sequence – Similar error for invalid character-level escape sequences (not byte-level).
• 22021 character_not_in_repertoire – Character cannot be represented in the target encoding.
• 22P05 untranslatable_character – Character cannot be translated between client and server encodings; often appears alongside 2200D in encoding mismatch scenarios.

---

📖 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.