PostgreSQL 22P06 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22P06: Nonstandard Use of Escape Character

PostgreSQL error code 22P06 is triggered when a backslash (\) is used as an escape character inside a regular string literal in a non-standard way. In standard SQL, a backslash inside a single-quoted string has no special meaning, but older PostgreSQL behavior treated it as an escape sequence initiator. This warning typically surfaces when migrating legacy applications to modern PostgreSQL versions or when escape_string_warning is enabled on your server.

---

Top 3 Causes

1. Using Backslash Escapes Without E'' Syntax

The most common cause is writing escape sequences like \n or \t in plain string literals without explicitly marking them as escape strings.

-- Problematic: triggers 22P06 warning
SELECT 'Hello\nWorld';

-- Correct: explicitly declare escape string
SELECT E'Hello\nWorld';

-- Correct: use standard SQL approach
SELECT 'Hello' || chr(10) || 'World';

-- Check your current settings
SHOW standard_conforming_strings;
SHOW escape_string_warning;

2. Legacy Application Queries With Unescaped Backslashes

Old ORMs and frameworks (older Hibernate, PHP PDO, etc.) sometimes generate SQL that relies on PostgreSQL's non-standard backslash behavior. When standard_conforming_strings is on (default since PostgreSQL 9.1), these queries can trigger 22P06.

-- Legacy generated query (problematic)
SELECT * FROM users WHERE note = 'It\'s a test';

-- Modern correct approach
SELECT * FROM users WHERE note = 'It''s a test';

-- Apply standard mode at the database level
ALTER DATABASE myapp SET standard_conforming_strings = on;

-- Verify settings
SELECT name, setting, source
FROM pg_settings
WHERE name IN ('standard_conforming_strings', 'escape_string_warning');

3. File Paths and Regex Patterns With Raw Backslashes

Windows file paths and regular expressions frequently contain backslashes that developers paste directly into SQL strings without proper handling.

-- Problematic: raw Windows path
SELECT * FROM files WHERE path = 'C:\Users\data\report.csv';

-- Correct: use E'' with doubled backslashes
SELECT * FROM files WHERE path = E'C:\\Users\\data\\report.csv';

-- Correct: dollar quoting (great for regex)
SELECT * FROM logs WHERE message ~ $$\d{3}-\d{4}$$;

-- Correct: E'' for regex
SELECT * FROM logs WHERE message ~ E'\\d{3}-\\d{4}';

---

Quick Fix Solutions

-- Option 1: Enable standard conforming strings (recommended)
SET standard_conforming_strings = on;

-- Option 2: Suppress the warning temporarily (not ideal long-term)
SET escape_string_warning = off;

-- Option 3: Apply fix permanently at the role level
ALTER ROLE app_user SET standard_conforming_strings = on;

-- Option 4: Use dollar quoting to avoid backslash issues entirely
SELECT $$This has a \backslash and it's fine$$;

---

Prevention Tips

1. Enforce standard_conforming_strings = on globally in your postgresql.conf. This has been the default since PostgreSQL 9.1, and explicitly setting it removes ambiguity. Always use E'' syntax when you genuinely need escape sequences, and use dollar quoting ($$) for complex strings like regex patterns or PL/pgSQL function bodies.

2. Use parameterized queries (prepared statements) in all application code. This completely eliminates the need to manually escape strings in SQL, preventing 22P06 and SQL injection at the same time. Let your database driver handle escaping automatically.
   

-- Instead of building raw SQL strings, use parameterized queries
-- Example in psql for testing prepared statements
PREPARE find_user (text) AS
    SELECT * FROM users WHERE username = $1;

EXECUTE find_user('john\doe');

---

Related Errors

• 22025 (invalid_escape_sequence): Occurs when an invalid escape sequence is used inside an E'' string. Often appears alongside 22P06 during migrations.
• 42601 (syntax_error): Improper backslash usage can confuse the SQL parser into misreading string boundaries, leading to syntax errors.

---

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