PostgreSQL 22P05 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22P05: untranslatable character

PostgreSQL error code 22P05 untranslatable character occurs when the database server cannot convert a character from the client encoding into the server's encoding. This typically happens when there is a mismatch between what the client sends and what the server's encoding can represent, such as attempting to store an emoji or a multibyte character in a LATIN1 or SQL_ASCII database. Understanding encoding configurations is the key to resolving and preventing this error.

Top 3 Causes

1. Client and Server Encoding Mismatch

The most common cause is a difference between the client's encoding and the database server's encoding. When the server is configured with SQL_ASCII or LATIN1 but the client sends UTF-8 encoded multibyte characters, the server cannot translate them.

-- Check current encodings
SHOW server_encoding;
SHOW client_encoding;

-- Check the encoding of a specific database
SELECT datname, pg_encoding_to_char(encoding) AS encoding
FROM pg_database
WHERE datname = current_database();

-- Fix: Set client encoding to match the server for the current session
SET client_encoding TO 'UTF8';

-- Fix: Set it permanently for a specific database
ALTER DATABASE mydb SET client_encoding TO 'UTF8';

2. Inserting Multibyte Characters into a SQL_ASCII Database

SQL_ASCII in PostgreSQL is not a true encoding — it disables encoding validation entirely and only safely stores standard ASCII (0–127). Any character with a byte value above 127, such as Korean, Chinese, Japanese text, or emoji, can trigger error 22P05.

-- Check if your database uses SQL_ASCII
SELECT pg_encoding_to_char(encoding)
FROM pg_database
WHERE datname = current_database();

-- Long-term fix: Create a new UTF-8 database
CREATE DATABASE mydb_new
    ENCODING 'UTF8'
    LC_COLLATE 'en_US.UTF-8'
    LC_CTYPE 'en_US.UTF-8'
    TEMPLATE template0;

-- Workaround: Strip non-ASCII characters before inserting
INSERT INTO my_table (text_column)
VALUES (regexp_replace('Hello 🎉 World', '[^\x00-\x7F]', '', 'g'));

3. Incorrect Encoding in File Imports or ETL Pipelines

When importing external files (CSV, JSON, etc.) via COPY or other ETL tools without explicitly specifying the encoding, PostgreSQL may encounter characters it cannot translate. This is especially common when processing files created on Windows systems using encodings like CP949 or EUC-KR.

-- Always specify encoding explicitly with COPY
COPY my_table (col1, col2, col3)
FROM '/path/to/data.csv'
WITH (
    FORMAT CSV,
    HEADER true,
    ENCODING 'UTF8'
);

-- For EUC-KR encoded files (common in Korean legacy systems)
COPY my_table (col1, col2)
FROM '/path/to/korean_data.csv'
WITH (
    FORMAT CSV,
    HEADER true,
    ENCODING 'EUC_KR'
);

-- Detect non-ASCII characters in existing data
SELECT id, text_column
FROM my_table
WHERE text_column ~ '[^\x00-\x7F]';

-- Replace untranslatable characters with a placeholder
UPDATE my_table
SET text_column = regexp_replace(text_column, '[^\x00-\x7F]', '?', 'g')
WHERE text_column ~ '[^\x00-\x7F]';

Quick Fix Solutions

-- 1. Temporarily adjust client encoding for your session
SET client_encoding TO 'UTF8';

-- 2. Use convert_from / convert_to for explicit byte-level handling
SELECT convert_from('\xed959c\xea b8\x80'::bytea, 'UTF8');

-- 3. Use the convert() function to recode data
SELECT convert('some text'::bytea, 'UTF8', 'LATIN1');

-- 4. Apply regex to sanitize data before inserting
SELECT regexp_replace(input_text, '[^\x00-\x7F]', '', 'g')
FROM staging_table;

Prevention Tips

Always create databases with UTF-8 encoding. Make UTF8 your organization's standard for all new databases and explicitly set client_encoding=UTF8 in all application connection strings (JDBC, psycopg2, node-postgres, etc.). You can also enforce this at the database level:

   ALTER DATABASE mydb SET client_encoding TO 'UTF8';
   ALTER SYSTEM SET client_encoding = 'UTF8';
   SELECT pg_reload_conf();

Validate and sanitize encoding at the application layer before data reaches PostgreSQL. In Python, use text.encode('utf-8', errors='replace').decode('utf-8') to neutralize problematic characters early. Set up log monitoring for 22P05 errors in postgresql.conf by enabling log_min_error_statement so you can catch encoding issues as soon as they surface in production.

Related Errors

22021 character_not_in_repertoire — Similar encoding issue; character not supported by the defined character repertoire.
22000 data_exception — Parent category for data-related errors, including encoding failures.
22P06 nonstandard_use_of_escape_character — Triggered by non-standard escape sequences, often seen alongside special character handling issues.

📖 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