How to fix Untranslatable character in PostgreSQL

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

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.

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

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.

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

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:

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

The -c flag omits characters that cannot be converted.

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

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

This ensures PostgreSQL handles the conversion correctly.

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

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

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

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

This helps diagnose encoding mismatches.

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

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

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