DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22019 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22019: Invalid Escape Character

PostgreSQL error code 22019 (invalid_escape_character) is raised when the ESCAPE clause in a LIKE or SIMILAR TO expression receives a value that is not exactly one character long. The SQL standard strictly requires the escape character to be a single character — an empty string or a multi-character string will both trigger this error immediately.

Top 3 Causes

1. Empty or Multi-Character String in ESCAPE Clause

The most common cause is passing an empty string or a string with more than one character to the ESCAPE clause.

-- ❌ Empty string as escape character
SELECT * FROM products
WHERE name LIKE '50\%' ESCAPE '';

-- ❌ Two-character escape sequence
SELECT * FROM products
WHERE name LIKE '50!_%' ESCAPE '!!';

-- ✅ Correct: exactly one character
SELECT * FROM products
WHERE name LIKE '50!%' ESCAPE '!';
Enter fullscreen mode Exit fullscreen mode

2. Dynamically Generated Queries Without Input Validation

When applications or ORMs build SQL queries dynamically, the escape character value may come from user input or a variable that hasn't been validated for length. This often surfaces in production when edge-case inputs are entered.

-- ✅ Safe PL/pgSQL function with validated escape character
CREATE OR REPLACE FUNCTION search_items(p_keyword TEXT)
RETURNS TABLE(id INT, name TEXT) AS $$
DECLARE
    v_esc CHAR(1) := '!';
    v_pattern TEXT;
BEGIN
    -- Safely escape special LIKE characters in the input
    v_pattern := replace(replace(replace(p_keyword, '!', '!!'), '%', '!%'), '_', '!_');

    RETURN QUERY
    SELECT i.id, i.name
    FROM items i
    WHERE i.name LIKE '%' || v_pattern || '%' ESCAPE v_esc;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT * FROM search_items('50% off');
SELECT * FROM search_items('size_M');
Enter fullscreen mode Exit fullscreen mode

3. Confusion with standard_conforming_strings Setting

Since PostgreSQL 9.1, standard_conforming_strings defaults to on, meaning backslashes are treated as literal characters, not escape sequences. Developers accustomed to older behavior may write patterns that result in an unintended multi-character value being passed to ESCAPE.

-- Check current setting
SHOW standard_conforming_strings;

-- ❌ Can cause confusion when standard_conforming_strings = on
SELECT * FROM orders WHERE ref LIKE E'100\\%' ESCAPE E'\\';

-- ✅ Avoid confusion by using a non-backslash escape character
SELECT * FROM orders WHERE ref LIKE '100#%' ESCAPE '#';

-- ✅ SIMILAR TO example with proper escape
SELECT * FROM users WHERE email SIMILAR TO '%(gmail|yahoo)\.com' ESCAPE '\';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Create a reusable utility function to safely escape LIKE patterns across your entire codebase:

CREATE OR REPLACE FUNCTION escape_like(
    p_input TEXT,
    p_esc   CHAR(1) DEFAULT '!'
)
RETURNS TEXT AS $$
BEGIN
    RETURN replace(
               replace(
                   replace(p_input, p_esc, p_esc || p_esc),
                   '%', p_esc || '%'
               ),
               '_', p_esc || '_'
           );
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

-- Test it
SELECT escape_like('hello_world%test');
-- Returns: hello!_world!%test

-- Use in a query
SELECT * FROM products
WHERE name LIKE '%' || escape_like('50% discount') || '%' ESCAPE '!';
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Standardize your escape character: Choose one escape character (e.g., !) for your entire project and enforce it through a shared utility function like escape_like() above. Document this in your team's coding conventions.

  • Always use parameterized queries: Never interpolate raw user input directly into SQL strings. Use prepared statements and parameter binding in your application layer. Add unit tests that cover edge cases such as empty strings, %, _, and multi-character inputs to catch this error before it reaches production.

Related Errors

Code Name Description
22025 invalid_escape_sequence Valid escape character, but the sequence itself is malformed (e.g., E'\q')
2200C invalid_use_of_escape_character Escape character used in a context where it is not permitted
42601 syntax_error Completely malformed escape syntax may surface as a syntax error instead

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