PostgreSQL 22025 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22025: Invalid Escape Sequence

PostgreSQL error code 22025 (invalid_escape_sequence) occurs when an unrecognized or malformed escape sequence is used inside a string literal or a LIKE/SIMILAR TO pattern. This error became more common after PostgreSQL 9.1 when standard_conforming_strings was switched to on by default, changing how backslashes are interpreted. Understanding the distinction between regular string literals and escape string literals (E'') is essential to resolving this error.

---

Top 3 Causes

1. Incorrect Backslash Usage in LIKE Patterns

Using \ as an escape character in LIKE without an explicit ESCAPE clause is a frequent mistake, especially in code migrated from MySQL.

-- ❌ Causes error 22025
SELECT * FROM products WHERE description LIKE '%50\%off%';

-- ✅ Correct: use ESCAPE clause explicitly
SELECT * FROM products WHERE description LIKE '%50!%off%' ESCAPE '!';

-- ✅ Also valid: explicitly declare backslash as escape char
SELECT * FROM products WHERE description LIKE '%50\%off%' ESCAPE '\';

2. Invalid Sequences in E'' String Literals

When using the escape string syntax E'...', only PostgreSQL-recognized sequences like \n, \t, \r, \\, and \' are valid. Using undefined sequences such as \q or \s triggers error 22025.

-- ❌ Causes error: \q is not a valid escape sequence
SELECT E'\q hello world';

-- ❌ Also invalid
INSERT INTO logs (msg) VALUES (E'Error\s in process');

-- ✅ Correct: use only supported escape sequences
SELECT E'\n hello world';           -- newline
SELECT E'\t hello world';           -- tab
SELECT E'Error\\ occurred';         -- literal backslash
INSERT INTO logs (msg) VALUES (E'Line1\nLine2\nLine3');

3. Missing ESCAPE Clause When Searching for Special Characters

When you need to search for literal % or _ characters using LIKE, failing to provide an ESCAPE clause and instead using ad-hoc backslash escaping causes this error.

-- ❌ Causes error: backslash not a defined escape without ESCAPE clause
SELECT * FROM users WHERE code LIKE '50\%';
SELECT * FROM files WHERE name LIKE 'file\_backup';

-- ✅ Correct: always pair special char escaping with ESCAPE clause
SELECT * FROM users WHERE code LIKE '50|%' ESCAPE '|';
SELECT * FROM files WHERE name LIKE 'file|_backup' ESCAPE '|';

-- ✅ Reusable helper function for dynamic LIKE searches
CREATE OR REPLACE FUNCTION escape_like(p_text text, p_escape_char text DEFAULT '!')
RETURNS text AS $$
  SELECT replace(
    replace(
      replace(p_text, p_escape_char, p_escape_char || p_escape_char),
      '%', p_escape_char || '%'
    ),
    '_', p_escape_char || '_'
  );
$$ LANGUAGE sql IMMUTABLE STRICT;

-- Usage
SELECT * FROM users
WHERE username LIKE '%' || escape_like('admin_user') || '%' ESCAPE '!';

---

Quick Fix Solutions

Check your current session settings:

-- Verify escape-related configuration
SHOW standard_conforming_strings;  -- should be 'on' in modern PostgreSQL
SHOW escape_string_warning;

-- Enable warnings to catch potential issues early
SET escape_string_warning = on;

-- Apply at database level
ALTER DATABASE mydb SET standard_conforming_strings = on;
ALTER DATABASE mydb SET escape_string_warning = on;

Use parameterized queries to avoid the problem entirely:

-- Use prepared statements instead of string concatenation
PREPARE safe_search(text) AS
  SELECT * FROM users WHERE username LIKE '%' || $1 || '%';

EXECUTE safe_search('john_doe');
DEALLOCATE safe_search;

---

Prevention Tips

1. Always use parameterized queries / prepared statements in application code. Libraries like psycopg2 (Python), node-postgres, or JDBC handle escaping correctly when you use bind parameters, eliminating the risk of error 22025 entirely.

2. Enable escape_string_warning = on in postgresql.conf for all environments. This logs a warning whenever a potentially problematic escape sequence is detected, letting you catch issues before they become runtime errors. Additionally, standardize on standard_conforming_strings = on (the default since PostgreSQL 9.1) across all environments to ensure consistent backslash behavior.
   

-- Recommended postgresql.conf settings
-- standard_conforming_strings = on
-- escape_string_warning = on

-- Verify no legacy escape patterns exist in your codebase
-- Search for E'' string literals in your SQL files and review each one
SELECT query FROM pg_stat_statements
WHERE query LIKE '%E''%' OR query LIKE '%ESCAPE%'
ORDER BY calls DESC;

---

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