How to fix 22019: invalid_escape_character in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 128 views

PostgreSQL error 22019 occurs when an invalid escape character is used in a LIKE pattern or string literal. This typically happens when the escape character in a LIKE ESCAPE clause is longer than one character or when backslash escapes are used incorrectly.

Quick fix

Fixes 22019

  1. Verify the ESCAPE clause uses single character
  2. Use valid escape character in LIKE
  3. Fix backslash escapes in string literals
  4. Check standard_conforming_strings setting
  5. Remove unnecessary escaping
postgresql — error
# 22019: invalid_escape_character
22019: invalid_escape_character

What this error means

The "invalid_escape_character" error (SQLSTATE 22019) is a data exception error that occurs when PostgreSQL encounters an invalid escape character in pattern matching or string operations. Escape characters in PostgreSQL serve specific purposes: they allow special characters to be included literally in LIKE patterns or to introduce escape sequences in strings. However, escape characters must follow strict syntax rules. This error typically happens when: 1. The ESCAPE clause in a LIKE expression specifies a character that is longer than one byte 2. The escape character in a LIKE pattern is invalid or improperly specified 3. Backslash escapes are used in regular strings without the E prefix (when standard_conforming_strings is on) 4. Invalid escape sequences appear in string literals

Typical symptoms

  • SQL queries with LIKE ESCAPE fail with error "22019: invalid_escape_character"
  • Pattern matching expressions throw database errors
  • Queries containing backslash escapes fail unexpectedly
  • Application code using LIKE clauses with escape characters fails
  • Search or filter features in application stop working
  • Queries work in one PostgreSQL version but fail in another

Common causes

  1. ESCAPE clause specifies a multi-character string instead of single character
  2. Escape character is not a valid single-byte character
  3. Backslash escapes used in regular string literals without E prefix
  4. standard_conforming_strings setting is enabled (PostgreSQL 9.1+)
  5. Incorrect syntax in LIKE pattern escape specification
  6. NULL or empty string used as escape character
  7. Migrating queries from older PostgreSQL versions without proper escaping

How to fix "22019: invalid_escape_character"

1Verify the ESCAPE clause uses single character

The ESCAPE clause must specify exactly one character. Check your LIKE patterns:

Wrong (multi-character escape):
SELECT * FROM table WHERE column LIKE '%test%' ESCAPE 'ab';

Wrong (empty escape):
SELECT * FROM table WHERE column LIKE '%test%' ESCAPE '';

Correct (single character):
SELECT * FROM table WHERE column LIKE '%test%' ESCAPE '\\';

2Use valid escape character in LIKE

Choose a single character that does not appear in your pattern:

Using backslash:
SELECT * FROM table WHERE column LIKE '%\\_%' ESCAPE '\\'; -- Find underscore

Using exclamation mark:
SELECT * FROM table WHERE column LIKE '%!%%' ESCAPE '!'; -- Find percent sign

Using pipe:
SELECT * FROM table WHERE column LIKE '%|_%' ESCAPE '|';

Common choices: \\, !, |, #, ^ (any ASCII character not in your search pattern)

3Fix backslash escapes in string literals

When using backslash escapes in strings, use E prefix or double the backslash:

Wrong (with standard_conforming_strings = on):
SELECT 'line1\\\\nline2';

Correct (E prefix):
SELECT E'line1\\\\nline2';

Alternative (doubled backslash):
SELECT 'line1\\\\\\\\nline2';

4Check standard_conforming_strings setting

Verify the current setting:
SHOW standard_conforming_strings;

If 'on' (default in PostgreSQL 9.1+), all escape sequences in regular strings require E prefix.

To verify and show setting:
SHOW standard_conforming_strings;
SHOW escape_string_warning;

For troubleshooting:
SET standard_conforming_strings = on; -- Enforce strict escaping
SET escape_string_warning = on; -- Warn about backslashes

5Remove unnecessary escaping

Often the simplest fix is to avoid escapes. Only use ESCAPE when you need to search for % or _:

Instead of:
SELECT * FROM table WHERE column LIKE '%\\\\%' ESCAPE '\\\\';

Use simpler alternative:
SELECT * FROM table WHERE column LIKE '%\\\\%';

Or just check for literal text:
SELECT * FROM table WHERE column LIKE '%percent%';

6Use parameterized queries

Use parameterized queries or prepared statements to avoid escaping issues:

PostgreSQL with parameterized query:
PREPARE stmt AS
 SELECT * FROM table WHERE column LIKE $1 ESCAPE $2;
EXECUTE stmt('%test%', '!');

Using application drivers (e.g., Node.js):
const query = 'SELECT * FROM table WHERE column LIKE $1 ESCAPE $2';
const result = await db.query(query, ['%test%', '!']);

This lets the driver handle escaping correctly.

7Test the corrected queries

Test your fixes:

Test basic LIKE with single-char escape:
SELECT * FROM table WHERE column LIKE 'a!%b' ESCAPE '!';

Test finding special characters:
SELECT * FROM table WHERE column LIKE '%!_%' ESCAPE '!';

Test string escapes:
SELECT E'Test\\\\nMultiline';

Enable warnings to catch issues:
SET escape_string_warning = on;

Advanced notes

PostgreSQL Escape Rules:

1. ESCAPE in LIKE: Must be exactly one character. Common choices: backslash (\\), exclamation (!), pipe (|), hash (#), caret (^).

2. Backslash Escapes: Require E prefix in standard-conforming mode (PostgreSQL 9.1+). Valid sequences: \\\\b (backspace), \\\\f (form feed), \\\\n (newline), \\\\r (carriage return), \\\\t (tab), \\\\xHH (hex), \\\\oOOO (octal).

3. Alternative Quoting: Use dollar-quoting to avoid escaping: $$string$$ or $tag$string$tag$.

4. Unicode Escapes: Use U& prefix for Unicode: U&'d\\\\0061ta' (requires UESCAPE for custom escape char).

5. Historical Note: PostgreSQL 8.1 and earlier had standard_conforming_strings = off by default, allowing backslash escapes in regular strings.

6. Migration: Audit old SQL when upgrading PostgreSQL versions. Use escape_string_warning to identify problematic queries.

7. Best Practice: Use parameterized queries or prepared statements. Drivers handle escaping automatically, avoiding these errors.

Frequently asked questions

What does "22019" mean?
The "invalid_escape_character" error (SQLSTATE 22019) is a data exception error that occurs when PostgreSQL encounters an invalid escape character in pattern matching or string operations. Escape characters in PostgreSQL serve specific purposes: they allow special characters to be included literally in LIKE patterns or to introduce escape sequences in strings. However, escape characters must follow strict syntax rules.
How do I fix "22019"?
PostgreSQL error 22019 occurs when an invalid escape character is used in a LIKE pattern or string literal. This typically happens when the escape character in a LIKE ESCAPE clause is longer than one character or when backslash escapes are used incorrectly.
What causes "22019"?
ESCAPE clause specifies a multi-character string instead of single character Escape character is not a valid single-byte character Backslash escapes used in regular string literals without E prefix
Is the "22019" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading