PostgreSQL 22024 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22024: unterminated_c_string

PostgreSQL error 22024 (unterminated_c_string) occurs when the database parser encounters a C-style string literal that does not have a proper terminating character. This typically happens when escape sequences are malformed, null bytes (\0) are embedded in string data, or client encoding mismatches cause the string to be parsed incorrectly. It is especially common during data migrations, ETL pipelines, and integrations with external systems that generate raw or binary data.

Top 3 Causes and Fixes

1. Malformed Escape Sequences

When using PostgreSQL's escape string syntax (E''), a backslash at the end of a string or an invalid escape sequence causes the parser to lose track of where the string ends.

-- BAD: backslash at end causes unterminated c string
SELECT E'C:\Users\name\';

-- GOOD: double the backslash to escape it properly
SELECT E'C:\\Users\\name\\';

-- BEST: use dollar quoting to avoid escaping entirely
SELECT $$C:\Users\name\$$;

-- Dollar quoting in stored procedures (recommended)
CREATE OR REPLACE FUNCTION safe_path()
RETURNS text AS $$
BEGIN
    RETURN 'C:\Program Files\PostgreSQL';
END;
$$ LANGUAGE plpgsql;

Fix: Always prefer dollar quoting ($$) for string literals that contain backslashes or special characters. Set standard_conforming_strings = on in postgresql.conf to prevent backslashes from being treated as escape characters by default.

-- Check and enforce standard conforming strings
SHOW standard_conforming_strings;
SET standard_conforming_strings = on;

2. Null Bytes Embedded in String Data

PostgreSQL's text and varchar types use C-style null-termination internally. If a string contains a null byte (chr(0)), the parser treats it as the end of the string, causing the 22024 error. This is extremely common when migrating data from legacy systems or importing binary content into text columns.

-- Detect rows containing null bytes
SELECT id, position(chr(0) IN content) AS null_pos
FROM raw_data
WHERE content LIKE '%' || chr(0) || '%';

-- Strip null bytes before insertion
INSERT INTO clean_table (content)
SELECT replace(content, chr(0), '')
FROM raw_data;

-- Update existing rows to remove null bytes
UPDATE my_table
SET text_column = replace(text_column, chr(0), '')
WHERE text_column LIKE '%' || chr(0) || '%';

-- Use bytea for true binary data instead of text
CREATE TABLE binary_data (
    id serial PRIMARY KEY,
    payload bytea
);

INSERT INTO binary_data (payload)
VALUES (decode('48656C6C6F00576F726C64', 'hex'));

Fix: Always sanitize incoming data before inserting into text columns. Store binary or mixed data in bytea columns instead of text or varchar.

3. Client/Server Encoding Mismatch

When the client encoding does not match the server encoding, multi-byte characters can be split across byte boundaries, causing the string parser to misread the end of the string. This is common with non-ASCII data (Korean, Japanese, Chinese) and outdated client libraries.

-- Check current encoding settings
SHOW server_encoding;
SHOW client_encoding;
SELECT pg_client_encoding();

-- Explicitly set client encoding for the session
SET client_encoding TO 'UTF8';

-- Validate and convert encoding safely
SELECT convert_from(
    convert_to(raw_text, 'LATIN1'),
    'UTF8'
)
FROM source_table;

-- Identify rows with encoding issues
SELECT id,
       octet_length(content) AS byte_length,
       length(content)       AS char_length
FROM my_table
WHERE octet_length(content) != length(content);

Fix: Ensure client_encoding matches server_encoding (ideally both UTF8). Always use parameterized queries instead of string concatenation to avoid encoding-related injection issues.

Quick Prevention Tips

Always use parameterized queries. Never concatenate user input directly into SQL strings. Parameterized queries let the driver handle encoding and escaping correctly, eliminating the root cause of most 22024 errors.

-- Add a trigger to block null bytes at the database level
CREATE OR REPLACE FUNCTION reject_null_bytes()
RETURNS trigger AS $$
BEGIN
    IF NEW.content LIKE '%' || chr(0) || '%' THEN
        RAISE EXCEPTION 'null byte not allowed in content';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_no_null_bytes
BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION reject_null_bytes();

Standardize encoding across your stack. Set standard_conforming_strings = on and enforce UTF8 encoding on both the server and all clients. Document and audit encoding settings whenever onboarding a new application or data source.

Related Errors

Error Code	Name	Relationship
22021	character_not_in_repertoire	Encoding errors, often co-occurs with 22024
22P02	invalid_text_representation	Invalid format when casting text types
42601	syntax_error	Can appear instead of 22024 when escape breaks SQL syntax
22000	data_exception	Parent category of 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.

DEV Community