📋

Description

This `22P05` error signifies a `Data Exception` where PostgreSQL encounters a character that cannot be represented in the target character encoding. It commonly occurs during data insertion or update operations when there's an encoding mismatch between the input data and the database, table, or column.

💬

Error Message

untranslatable character

🔍

Known Causes

3 known causes

⚠️

Encoding Mismatch

The character encoding of the data being inserted differs from the encoding configured for the database, table, or specific column.

⚠️

Invalid Character Input

Attempting to store characters that are inherently not representable within the defined character set of the target column or database.

⚠️

Client-Server Encoding Discrepancy

The client application's connection encoding setting does not match the encoding expected by the PostgreSQL server for the current session.

🛠️

Solutions

3 solutions available

1. Identify and Remove Untranslatable Characters medium

Locate problematic characters in your data and remove or replace them.

1

Identify the table and column(s) that might contain untranslatable characters. This often occurs when data is inserted from a source with a different character encoding or if there are control characters.

2

Use a SQL query to find rows containing characters that cannot be represented in your database's encoding. This example assumes your database encoding is UTF8. Adjust the `pg_encoding_to_char` function if your encoding is different. The `translate` function is used to remove common problematic characters; you may need to expand this list.

SELECT *
FROM your_table
WHERE your_column ~ '[^\x00-\xFF]' -- Simple check for non-ASCII characters
OR your_column ~ E'[\x00-\x08\x0B\x0C\x0E-\x1F\x7F-\x9F]'; -- Check for control characters

3

Once identified, update the problematic rows to remove or replace the untranslatable characters. The `regexp_replace` function is powerful here. This example removes control characters and common untranslatable characters.

UPDATE your_table
SET your_column = regexp_replace(
    your_column,
    E'[\x00-\x08\x0B\x0C\x0E-\x1F\x7F-\x9F]'
    || E'[\u200B\u200C\u200D\u2060\uFEFF]', -- Zero-width spaces and similar
    '',
    'g'
);

-- For specific known untranslatable characters, you might need a more targeted approach:
-- UPDATE your_table
-- SET your_column = translate(your_column, E'\240\250\260\270', ' '); -- Example for specific octal codes

4

Commit the transaction to save the changes.

COMMIT;

2. Set Client Encoding to Match Data Source easy

Ensure your application's connection encoding matches the data's origin to prevent conversion issues.

1

Determine the character encoding of the data you are trying to insert or retrieve. Common encodings include UTF-8, LATIN1, and WINDOWS-1252.

2

Configure your PostgreSQL client or application to use the correct encoding. This is often done at the connection string level or via a `SET client_encoding` command.

SET client_encoding = 'UTF8'; -- Or 'LATIN1', 'WINDOWS-1252', etc.

3

If you are using a programming language, ensure its database driver is also configured for the correct encoding. For example, in Python with `psycopg2`:

conn = psycopg2.connect(
    dbname='your_db',
    user='your_user',
    password='your_password',
    host='your_host',
    options='-c client_encoding=UTF8'
)

4

Retry the operation that was failing. If the client encoding now matches the data's encoding, the untranslatable character error should be resolved.

3. Alter Database Character Encoding (Advanced) advanced

Change the database's default encoding to support a wider range of characters.

1

This is a significant change and requires careful planning. It's generally recommended to start with a new database with the desired encoding.

2

Backup your entire database before proceeding.

3

Stop all applications and clients connected to the database.

4

Use `ALTER DATABASE` to change the encoding. UTF8 is highly recommended for broad character support.

ALTER DATABASE your_database_name ENCODING 'UTF8';

5

Re-create or re-import your data into the database after changing the encoding. You might need to use `pg_dump` and `pg_restore` with appropriate encoding options.

6

Start your applications and clients again.

🔗

Related Errors

5 related errors

PostgreSQL Error 22P05: Untranslatable Character Data