PostgreSQL 22024 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22024: unterminated c string

PostgreSQL error code 22024 - unterminated c string occurs when the SQL parser encounters a C-style string that doesn't have a proper termination point. This typically happens when escape sequences using backslashes are malformed, or when null bytes are embedded in string literals. It's a common issue in legacy applications, data migrations, and any environment where raw string concatenation is used to build SQL queries.

---

Top 3 Causes

1. Malformed Escape Sequences in String Literals

When using escape strings (prefixed with E'') in PostgreSQL, an improperly terminated backslash sequence confuses the parser into thinking the string hasn't ended.

-- BAD: unterminated escape sequence causes 22024
-- SELECT E'Hello\' AS test;

-- GOOD: properly escaped backslash
SELECT E'Hello\\World' AS test;       -- Outputs: Hello\World
SELECT E'Tab\there' AS tabtest;        -- Outputs: Tab  here

-- Check your escape string setting
SHOW standard_conforming_strings;

-- Always prefer standard conforming strings (on by default since PG 9.1)
SET standard_conforming_strings = on;

-- With standard_conforming_strings ON, use standard quoting
SELECT 'It''s a safe string' AS safe_str;

2. Unsafe String Concatenation in Application Code

Directly concatenating user input into SQL queries without proper escaping is the most dangerous cause — it leads to both the 22024 error and SQL injection vulnerabilities.

-- DANGEROUS pattern (never do this in application code):
-- sql = "SELECT * FROM users WHERE name = '" + user_input + "'"

-- SAFE: Use parameterized queries from your application driver
-- In PL/pgSQL, always pass values as parameters

CREATE OR REPLACE FUNCTION find_user(p_username TEXT)
RETURNS TABLE(id INT, username TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT u.id, u.username
    FROM users u
    WHERE u.username = p_username;  -- safely bound parameter
END;
$$ LANGUAGE plpgsql;

-- For dynamic SQL inside PL/pgSQL, use quote_literal()
DO $$
DECLARE
    v_input TEXT := 'O''Brien';
    v_query TEXT;
BEGIN
    v_query := 'SELECT * FROM users WHERE name = '
               || quote_literal(v_input);
    RAISE NOTICE 'Safe query: %', v_query;
END;
$$;

3. Null Bytes or Invalid Characters During COPY / Data Migration

When loading data via COPY or ETL tools, source files (CSV, TSV) may contain null bytes (\x00) or stray escape sequences that PostgreSQL cannot parse correctly.

-- Detect rows with null bytes in a staging table
SELECT id, octet_length(content) AS bytes, length(content) AS chars
FROM staging_table
WHERE content ~ chr(0);

-- Strip null bytes before inserting into production
UPDATE staging_table
SET content = replace(content, chr(0), '')
WHERE content LIKE '%' || chr(0) || '%';

-- Clean control characters using regexp_replace
INSERT INTO production_table (id, content)
SELECT id,
       regexp_replace(content, '[\x00-\x08\x0B\x0C\x0E-\x1F]', '', 'g')
FROM staging_table;

-- COPY with explicit NULL handling
-- COPY my_table FROM '/data/import.csv'
-- WITH (FORMAT csv, HEADER true, NULL '');

---

Quick Fix Solutions

-- 1. Verify and enforce safe string settings
SELECT name, setting
FROM pg_settings
WHERE name = 'standard_conforming_strings';

-- 2. Use quote_literal / quote_nullable for dynamic SQL
SELECT quote_literal('It''s a test \\ string');

-- 3. Remove problematic characters from existing data
UPDATE my_table
SET description = regexp_replace(description,
    '[\x00-\x1F\x7F]', '', 'g')
WHERE description ~ '[\x00-\x1F\x7F]';

-- 4. Add a CHECK constraint to block bad input at DB level
ALTER TABLE documents
ADD CONSTRAINT chk_no_null_bytes
CHECK (content NOT LIKE '%' || chr(0) || '%');

---

Prevention Tips

1. Always use parameterized queries and keep standard_conforming_strings = on
Never build SQL by concatenating strings. Use prepared statements in your application driver (e.g., $1 placeholders in psycopg2, ? in JDBC). Ensure standard_conforming_strings = on is explicitly set in postgresql.conf to avoid legacy escape-mode surprises.

2. Validate and sanitize data before loading
Before any COPY or bulk insert operation, run a pre-flight check to detect null bytes and invalid control characters. Automate this as part of your ETL pipeline using a staging table with cleansing logic, and add CHECK constraints on critical text columns to act as a last line of defense.

---

Related Errors

Code	Name	Notes
22021	character_not_in_repertoire	Unsupported character for DB encoding
22P02	invalid_text_representation	Type conversion failure from bad strings
42601	syntax_error	Parser-level failure from unterminated literals
22000	data_exception	Parent error class covering 22024

---

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