How to fix Untranslatable character in PostgreSQL

PostgreSQLINTERMEDIATEHIGHUpdated Dec 26, 2025· 157 views

This error occurs when PostgreSQL cannot convert a character from the client encoding to the database encoding. Fix it by aligning encodings to UTF-8 or preprocessing data before import.

Quick fix

Fixes 22P05

SHOW server_encoding;
SHOW client_encoding;
  1. Check the current database and client encodings
  2. Set client encoding to UTF-8
  3. Validate and preprocess incoming data
  4. Use COPY with explicit encoding when importing
postgresql — error
SHOW server_encoding;
SHOW client_encoding;
Untranslatable character

What this error means

The "Untranslatable character" error (code 22P05) happens when PostgreSQL attempts to convert data from your client connection's encoding to the database's encoding and encounters a character that has no equivalent in the target encoding. For example, trying to insert a UTF-8 character like an em-dash (—) into a database using LATIN1 encoding will fail because LATIN1 cannot represent that character. PostgreSQL is strict about character encoding to maintain data integrity and prevent corruption.

Typical symptoms

  • Queries fail with "ERROR: 22P05: character ... has no equivalent in ..." during INSERT or UPDATE operations
  • Data import or COPY operations abort midway with encoding conversion errors
  • Application receives untranslatable character errors when storing text from user input or external files
  • Migration from one database system to PostgreSQL fails at certain records

Common causes

  1. Client encoding (client_encoding) does not match the database encoding (server_encoding), and data contains characters outside the target encoding's repertoire
  2. Importing data encoded in UTF-8 into a database created with LATIN1 or WIN1252 encoding
  3. File encoding mismatch when using COPY FROM — for example, a UTF-8 CSV file being imported into a LATIN1 database
  4. Receiving data from external APIs or sources with different character encodings than your database expects
  5. Using an incorrect encoding setting when establishing a database connection from an application

How to fix "Untranslatable character"

1Check the current database and client encodings

Connect to your PostgreSQL database and check both the server and client encodings:

sql
SHOW server_encoding;
SHOW client_encoding;

If they differ, this may be causing the problem. The server encoding is set at database creation time and cannot be changed without recreation. The client encoding can be changed per-session.

2Set client encoding to UTF-8

Change your client encoding to match a universal encoding like UTF-8, which supports a wide range of characters:

sql
SET client_encoding TO 'UTF8';

For application connections, set this in your connection string. For psql, use the -E UTF-8 flag or set your terminal encoding.

3Validate and preprocess incoming data

Before importing files, validate their encoding using command-line tools:

bash
file -I myfile.csv          # Check file encoding
iconv -f UTF-8 -t LATIN1 myfile.csv  # Test conversion

If conversion fails or produces errors, the file contains characters that cannot be represented in LATIN1. Use iconv to clean the file:

bash
iconv -f UTF-8 -t LATIN1 -c myfile.csv > cleaned_file.csv

The -c flag omits characters that cannot be converted.

4Use COPY with explicit encoding when importing

When using PostgreSQL's COPY command to import data, specify the file encoding explicitly:

sql
COPY my_table FROM '/path/to/file.csv' WITH (FORMAT csv, ENCODING 'UTF8');

This ensures PostgreSQL handles the conversion correctly.

5Use convert_from() for selective character handling

For problematic columns, use PostgreSQL's convert_from() function to handle encoding conversion within SQL:

sql
INSERT INTO my_table (name) VALUES (
  convert_from('\xe28093'::bytea, 'UTF8')
);

Alternatively, use convert_to() to inspect the byte representation of stored data:

sql
SELECT convert_to(name, 'SQL_ASCII') FROM my_table;

This helps diagnose encoding mismatches.

6Create new databases in UTF-8

If you control database creation, always use UTF-8 encoding to avoid future encoding issues:

sql
CREATE DATABASE mydb ENCODING 'UTF8' LOCALE 'C';

UTF-8 supports the full Unicode character set and eliminates many character conversion problems.

Advanced notes

PostgreSQL distinguishes between two character encoding errors: "character_not_in_repertoire" (22021) indicates corrupted bytes that cannot be decoded as valid characters in any encoding, while "untranslatable_character" (22P05) indicates a valid character that simply does not exist in the target encoding. If you have a legacy database using LATIN1 or WIN1252, be aware that converting to UTF-8 requires dumping and restoring the entire database — you cannot change the server encoding of an existing database. For systems with decomposed UTF-8 (where accented characters are stored as separate base + combining characters), there may be conversion issues to other encodings; in this case, consider normalizing to composed form first using the unaccent module. If using ODBC connections (e.g., Power BI), ensure your system DSN is configured for Unicode encoding, not ANSI.

Frequently asked questions

What does "22P05" mean?
The "Untranslatable character" error (code 22P05) happens when PostgreSQL attempts to convert data from your client connection's encoding to the database's encoding and encounters a character that has no equivalent in the target encoding. For example, trying to insert a UTF-8 character like an em-dash (—) into a database using LATIN1 encoding will fail because LATIN1 cannot represent that character. PostgreSQL is strict about character encoding to maintain data integrity and prevent corruption.
How do I fix "22P05"?
This error occurs when PostgreSQL cannot convert a character from the client encoding to the database encoding. Fix it by aligning encodings to UTF-8 or preprocessing data before import.
What causes "22P05"?
Client encoding (client_encoding) does not match the database encoding (server_encoding), and data contains characters outside the target encoding's repertoire Importing data encoded in UTF-8 into a database created with LATIN1 or WIN1252 encoding File encoding mismatch when using COPY FROM — for example, a UTF-8 CSV file being imported into a LATIN1 database
Is the "22P05" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading