How to fix 22019: invalid_escape_character in PostgreSQL

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 '\\';

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)

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';

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

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%';

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.

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;