PostgreSQL error 22P05: untranslatable_character – causes and fixes

What is PostgreSQL error 22P05 (untranslatable_character)?

Error 22P05 is thrown when PostgreSQL attempts to convert data from the session’s client_encoding to the database’s server_encoding and encounters a byte sequence that has no mapping in the target set.

The failure aborts the current statement and any surrounding transaction. Fixing it is critical because partial loads, failed ETL jobs, and broken application writes lead to missing or corrupt data.

What Causes This Error?

Mismatched encodings trigger most 22P05 incidents. A psql session may default to UTF8 while the database was created with LATIN1, causing non-ASCII characters to stall COPY or INSERT commands.

Data imported from CSV, JSON, or external APIs can contain smart quotes, emojis, or multibyte glyphs that the target encoding does not support.

Locale misconfiguration on application servers also injects incompatible byte sequences, especially when Java, Node.js, or Python default to UTF-8 but connection pools announce SQL_ASCII.

How to Fix PostgreSQL Error 22P05

First, confirm encodings with SHOW client_encoding; and SHOW server_encoding;.

They must match or be mutually convertible (e.g., UTF8 to LATIN9).

If conversion is impossible, change the session encoding: SET client_encoding TO 'UTF8'; before running COPY or INSERT.

When data must stay in an older LATIN database, preprocess files through iconv or use PostgreSQL’s convert_from() and convert_to() to replace unsupported characters.

Galaxy flags encoding mismatches in the editor, suggesting the correct SET client_encoding statement before you run the query, reducing runtime failures.

.

Common Scenarios and Solutions

COPY FROM STDIN fails – Add the ENCODING clause: COPY tbl FROM 'file.csv' WITH (FORMAT csv, ENCODING 'UTF8');

Batch inserts via ORM – Ensure the connection string sets client_encoding=UTF8 or switch the driver to binary protocol.

psql restores – Use pg_dump --encoding=UTF8 and psql -v ON_ERROR_STOP=1 to halt on the first bad character.

Best Practices to Avoid This Error

Create new databases in UTF8 whenever possible. UTF8 supports the full Unicode range and avoids downstream translation issues.

Validate incoming files with file -I or iconv -f UTF8 -t LATIN1 before loading. Reject or cleanse bad rows early.

Automate SET client_encoding in application pools or use Galaxy’s connection templates, which inherit the database’s server_encoding and warn on drift.

Related Errors and Solutions

ERROR: 22021 invalid byte sequence for encoding – The server cannot even parse the byte sequence; sanitize the data or set the correct client_encoding.

ERROR: 42P17 invalid object definition – Appears when encoding issues corrupt DDL scripts; run with the proper psql locale.

ERROR: 57P03 cannot connect to the server – Not encoding-related but surfaces when misconfiguration blocks reconnection after 22P05 aborts.