untranslatable character
SQLSTATE 22P05 is a Postgres-specific error raised when a character in the input cannot be translated from the client encoding to the server (database) encoding. This is more specific than 22021 — it applies when the character exists in the source encoding but has no equivalent in the target encoding.
- 1A Unicode character exists in the client data but has no equivalent codepoint in the server encoding (e.g., a Unicode emoji in a LATIN-1 database)
- 2Encoding conversion table does not include the specific character
Inserting emoji into a LATIN-1 database from a UTF-8 client.
-- client_encoding = UTF8, database encoding = LATIN1
INSERT INTO messages (body) VALUES ('Hello 🌍');expected output
ERROR: character with byte sequence 0xf0 0x9f 0x8c 0x8d in encoding "UTF8" has no equivalent in encoding "LATIN1"
Fix 1
Use a Unicode-capable database encoding (UTF-8)
WHEN When designing a new database that will handle multilingual or emoji content.
CREATE DATABASE mydb ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';
Why this works
UTF-8 can represent all Unicode codepoints, eliminating untranslatable character errors.
Fix 2
Strip or replace untranslatable characters before inserting
WHEN When the database encoding cannot be changed.
-- Remove characters outside LATIN-1 range in the application layer before sending to Postgres
Why this works
Pre-process the string in the application to remove or replace characters not representable in the target encoding.
✕ Set client_encoding to LATIN-1 to hide UTF-8 characters
This causes Postgres to misinterpret the bytes rather than converting them, leading to silent data corruption.
Class 22 — Data Exception (Postgres-specific)
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev