DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22025 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22025: Invalid Escape Sequence

PostgreSQL error code 22025 (invalid_escape_sequence) is thrown when the database engine encounters an improperly defined escape sequence within a string literal or pattern-matching expression. This commonly occurs with LIKE, ILIKE, or SIMILAR TO operators when the ESCAPE clause is misused, or when backslash sequences are handled incorrectly in string literals. Understanding how PostgreSQL's escape mechanisms work is essential for writing robust, portable SQL.

Top 3 Causes

1. Invalid ESCAPE Clause in LIKE Patterns

The most frequent cause is providing more than one character (or an empty string in some contexts) to the ESCAPE clause. SQL standard strictly requires exactly one character.

-- ❌ Wrong: ESCAPE with more than one character
SELECT * FROM orders
WHERE ref_code LIKE '100\%' ESCAPE '\\';
-- ERROR: 22025 invalid escape sequence

-- ✅ Correct: single character escape
SELECT * FROM orders
WHERE ref_code LIKE '100!%' ESCAPE '!';

-- ✅ Practical: escape user input safely
CREATE OR REPLACE FUNCTION escape_like(input TEXT, esc TEXT DEFAULT '!')
RETURNS TEXT AS $$
BEGIN
    RETURN REPLACE(REPLACE(REPLACE(input, esc, esc||esc), '%', esc||'%'), '_', esc||'_');
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

SELECT * FROM customers
WHERE name LIKE '%' || escape_like('John_Doe') || '%' ESCAPE '!';
Enter fullscreen mode Exit fullscreen mode

2. Backslash Escape Sequences in Standard String Literals

Since PostgreSQL 9.1, standard_conforming_strings defaults to on, meaning backslashes in plain '...' strings are treated literally — not as escape characters. Using \n, \t, etc., without the E'' prefix leads to unexpected behavior or errors.

-- ❌ Wrong: C-style escape in a standard string literal
INSERT INTO logs (message) VALUES ('line1\nline2');
-- Backslash is NOT interpreted; stored literally as 'line1\nline2'

-- ✅ Correct: use E'' prefix for escape sequences
INSERT INTO logs (message) VALUES (E'line1\nline2');

-- ✅ Correct: use dollar quoting to avoid escape issues entirely
INSERT INTO file_paths (path) VALUES ($$C:\Users\data\file.txt$$);

-- Check your current setting
SHOW standard_conforming_strings;
Enter fullscreen mode Exit fullscreen mode

3. Misused Escape Sequences in SIMILAR TO Expressions

SIMILAR TO uses SQL-standard regex syntax, which differs from POSIX regex. Developers often mix up the two syntaxes, applying invalid escape sequences.

-- ❌ Wrong: using POSIX-style escape inside SIMILAR TO
SELECT * FROM products
WHERE sku SIMILAR TO 'SKU\-[0-9]+';
-- ERROR: 22025 invalid escape sequence

-- ✅ Correct: use character class or explicit ESCAPE
SELECT * FROM products
WHERE sku SIMILAR TO 'SKU[-][0-9]+';

-- ✅ Correct: explicit ESCAPE clause
SELECT * FROM products
WHERE sku SIMILAR TO 'SKU!-[0-9]+' ESCAPE '!';

-- ✅ Recommended: use POSIX regex instead — more powerful and intuitive
SELECT * FROM products
WHERE sku ~ '^SKU-[0-9]+';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Scenario Fix
ESCAPE clause error Use exactly one character: ESCAPE '!'
\n, \t not working Prefix string with E: E'line\nbreak'
SIMILAR TO pattern error Switch to POSIX regex with ~ or ~*
Dynamic LIKE patterns Use a sanitizing function before binding

Prevention Tips

1. Always use parameterized queries and a dedicated escape function.
Never concatenate raw user input into LIKE patterns. Create a reusable escape function at the database level and enforce its use in application code and ORMs.

-- Register once, use everywhere
CREATE OR REPLACE FUNCTION safe_like(p TEXT) RETURNS TEXT AS $$
  SELECT REPLACE(REPLACE(REPLACE(p,'!','!!'), '%','!%'), '_','!_');
$$ LANGUAGE sql IMMUTABLE STRICT;

-- Application query pattern
SELECT * FROM users
WHERE username LIKE '%' || safe_like($1) || '%' ESCAPE '!';
Enter fullscreen mode Exit fullscreen mode

2. Standardize on standard_conforming_strings = on and enforce it in code reviews.
Add explicit checks to your team's SQL coding standards: always use E'' for escape sequences, always use a single-character ESCAPE clause, and prefer POSIX regex (~) over SIMILAR TO for complex pattern matching. Include escape sequence validation in your CI/CD pipeline's SQL linting step.

Related Errors

  • 22019 invalid_escape_character — Triggered when the escape character itself is invalid.
  • 42601 syntax_error — Sometimes raised instead of 22025 when the parser catches malformed escape syntax early.
  • 22021 character_not_in_repertoire — Related to invalid character encoding in escape sequences.

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

Top comments (0)