PostgreSQL 22P06 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22P06: Nonstandard Use of Escape Character

PostgreSQL error 22P06 is triggered when a backslash (\) is used as an escape character inside a regular string literal ('...') in a non-standard way. Since PostgreSQL 9.1, standard_conforming_strings defaults to on, meaning backslashes in ordinary strings are treated as literal characters, not escape sequences. When escape_string_warning is enabled and your code still uses \n, \t, or \' inside plain quotes, PostgreSQL raises this warning or error.

---

Top 3 Causes

1. Using Escape Sequences in Plain String Literals

The most common cause is writing escape sequences like \n or \t directly inside single-quoted strings without the E prefix.

-- Triggers 22P06 warning
SELECT 'Hello\nWorld';
SELECT 'Column1\tColumn2';

-- Correct: Use the E'' escape string syntax
SELECT E'Hello\nWorld';
SELECT E'Column1\tColumn2';

-- Alternative: Use chr() function (safest approach)
SELECT 'Hello' || chr(10) || 'World';
SELECT 'Column1' || chr(9) || 'Column2';

2. Using Backslash to Escape Single Quotes

Legacy code from MySQL or Oracle migrations often uses \' to escape single quotes inside strings, which is non-standard in PostgreSQL.

-- Wrong: Backslash-escaped quote (non-standard, triggers 22P06)
SELECT 'It\'s a problem';

-- Correct option 1: Standard SQL double-quote escaping
SELECT 'It''s a problem';

-- Correct option 2: Dollar quoting
SELECT $$It's a problem$$;

-- Correct option 3: E'' syntax
SELECT E'It\'s a problem';

-- Practical example in an INSERT
INSERT INTO notes (body) VALUES ('It''s working correctly now');

3. Misconfigured escape_string_warning or backslash_quote Settings

Sometimes the issue surfaces after a PostgreSQL upgrade or when deploying to a new server with different configuration defaults.

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

-- Verify behavior with a test query
DO $$
BEGIN
    RAISE NOTICE 'standard_conforming_strings: %', current_setting('standard_conforming_strings');
    RAISE NOTICE 'escape_string_warning: %', current_setting('escape_string_warning');
END;
$$;

-- Recommended settings in postgresql.conf:
-- standard_conforming_strings = on   (default since PG 9.1)
-- escape_string_warning = on         (keep warnings visible)
-- backslash_quote = safe_encoding    (default, most secure)

---

Quick Fix Solutions

-- Replace all non-standard escape patterns in your queries:

-- BEFORE (problematic)
SELECT * FROM logs WHERE message = 'Error:\nSomething went wrong';

-- AFTER (fixed)
SELECT * FROM logs WHERE message = E'Error:\nSomething went wrong';
-- or
SELECT * FROM logs WHERE message = 'Error:' || chr(10) || 'Something went wrong';

-- For stored procedures and functions, use dollar quoting
CREATE OR REPLACE FUNCTION log_message(msg TEXT)
RETURNS void AS $$
BEGIN
    INSERT INTO event_log (created_at, message)
    VALUES (now(), msg);
END;
$$ LANGUAGE plpgsql;

-- Call with properly escaped strings
SELECT log_message(E'Line1\nLine2');

---

Prevention Tips

1. Always use parameterized queries in application code. Never concatenate raw strings into SQL. Libraries like psycopg2 (Python), JDBC PreparedStatement (Java), and node-postgres (Node.js) handle escaping automatically, eliminating 22P06 entirely at the application layer.

2. Enforce SQL linting in your CI/CD pipeline. Tools like sqlfluff can detect non-standard escape patterns before they reach production. Add a team coding standard requiring E'' syntax for any string containing special characters, and keep standard_conforming_strings = on locked in your postgresql.conf to ensure consistent behavior across all environments.

---

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