How to fix Character with byte sequence 0xXX in encoding "Y" has no equivalent in encoding "Z" in PostgreSQL

PostgreSQLINTERMEDIATEHIGHUpdated Dec 29, 2025· 165 views

PostgreSQL error 22P05 occurs when a character cannot be converted between database encodings, typically when moving data from UTF-8 to a single-byte encoding like WIN1252 or LATIN1. Resolve this by switching to Unicode drivers, fixing invalid characters, or adjusting client-side encoding settings.

Quick fix

Fixes 22P05

-- Check current database encoding
SHOW client_encoding;
SHOW server_encoding;

-- Set client encoding to match server
SET client_encoding = 'UTF8';
  1. Switch ODBC driver from ANSI to Unicode
  2. Verify database and client encoding compatibility
  3. Identify and fix problematic characters in data
  4. For database migrations, re-export with correct encoding
postgresql — error
-- Check current database encoding
SHOW client_encoding;
SHOW server_encoding;
-- Set client encoding to match server
SET client_encoding = 'UTF8';
Character with byte sequence 0xXX in encoding "Y" has no equivalent in encoding "Z"

What this error means

Error 22P05 is a character encoding conversion error that occurs when PostgreSQL attempts to translate a character from one encoding to another and the target encoding has no valid representation for that character. This commonly happens when the database uses UTF-8 (which supports many international characters) but the client application or ODBC driver expects a narrower encoding like WIN1252 (Windows Latin-1) or LATIN1 (ISO 8859-1). For example, a "fullwidth comma" character (Unicode U+FF0C) exists in UTF-8 but has no equivalent in WIN1252, causing the conversion to fail. The error includes the hexadecimal byte sequence that cannot be converted, helping identify which specific character is causing the problem.

Typical symptoms

  • ODBC connection fails with encoding conversion error when querying data
  • Data retrieval from UTF-8 database fails when using ANSI ODBC driver
  • Character encoding mismatch between database and client application
  • Pg_dump restore fails with "has no equivalent in encoding" error during migration
  • Bulk data imports fail partway through with specific byte sequences mentioned
  • Characters display incorrectly or queries abort when international characters are involved

Common causes

  1. Using ODBC ANSI driver instead of Unicode driver when connecting to UTF-8 database
  2. Database migration from multi-byte encoding (UTF-8) to single-byte encoding (LATIN1, WIN1252) without character validation
  3. Client application or connection string specifies incompatible character set encoding
  4. Invalid or corrupted data in source database containing characters unsupported by target encoding
  5. Pg_dump performed with -E UTF8 flag on database with different native encoding, creating incompatible export
  6. Legacy data containing special characters (smart quotes, em-dashes, etc.) from third-party sources

How to fix "Character with byte sequence 0xXX in encoding "Y" has no equivalent in encoding "Z""

1Switch ODBC driver from ANSI to Unicode

The most common fix for ODBC connections is switching from the ANSI driver to the Unicode driver, which properly handles UTF-8 encoding.

1. Open ODBC Data Source Administrator:
 - Windows: Control Panel → Administrative Tools → ODBC Data Sources
 - Or search for "ODBC Data Sources" in Windows Start menu

2. Select your PostgreSQL DSN (or create a new one)

3. Click "Configure" and change the driver:
 - FROM: PostgreSQL ANSI(x64) or PostgreSQL ANSI(x86)
 - TO: PostgreSQL Unicode(x64) or PostgreSQL Unicode(x86) (matching your system architecture)

4. Click OK and test the connection

If using a System DSN (recommended), ensure you're creating it as a SYSTEM DSN rather than a USER DSN.

2Verify database and client encoding compatibility

Check what encoding your database uses and ensure your application client matches:

sql
-- Check current database encoding
SHOW client_encoding;
SHOW server_encoding;

-- Set client encoding to match server
SET client_encoding = 'UTF8';

For Python applications using psycopg2:

python
import psycopg2
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="user",
    password="password",
    client_encoding='utf8'
)

For Node.js applications using pg:

javascript
const { Client } = require('pg');
const client = new Client({
  connectionString: 'postgresql://user:password@localhost/mydb',
  statement_timeout: 30000,
  application_name: 'myapp'
});
3Identify and fix problematic characters in data

If the error persists, find which row contains the incompatible character:

sql
-- Find rows with non-ASCII characters (UTF-8 multi-byte sequences)
SELECT id, column_name 
FROM your_table 
WHERE column_name::bytea != convert(column_name::text, 'UTF8'::name, 'LATIN1'::name)::bytea;

Once identified, replace problematic characters with valid equivalents:

sql
-- Replace smart quotes with regular quotes
UPDATE your_table 
SET column_name = REPLACE(column_name, '\u201c', '\"')
WHERE column_name LIKE '%\u201c%';

-- Or replace with spaces
UPDATE your_table
SET column_name = REPLACE(column_name, '\u2013', '-')
WHERE column_name LIKE '%\u2013%';
4For database migrations, re-export with correct encoding

If this error occurred during pg_dump/pg_restore:

bash
# Dump FROM original encoding (do not force UTF-8)
pg_dump -E LATIN1 -U username original_database > backup.sql

# Restore TO target database
psql -U username -d target_database < backup.sql

Alternatively, fix encoding in the dump file before restore:

bash
# Convert dump file encoding
iconv -f UTF-8 -t LATIN1 backup.sql > backup_latin1.sql
psql -U username -d target_database < backup_latin1.sql

For direct database cloning:

bash
pg_dumpall -g | psql  # Dump globals
pg_dump original_db | psql target_db  # Clone database
5Set client_encoding on connection initialization

For application frameworks, set encoding at connection time to prevent mismatches:

Laravel (config/database.php):

php
'pgsql' => [
    'driver' => 'pgsql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', 5432),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'schema' => 'public',
    'sslmode' => 'prefer',
],

Java (connection properties):

java
Properties props = new Properties();
props.setProperty("user", "username");
props.setProperty("password", "password");
props.setProperty("stringtype", "unspecified");  // Handles encoding better
Connection conn = DriverManager.getConnection("jdbc:postgresql://host/db", props);

Advanced notes

Character Encoding Fundamentals: Single-byte encodings like LATIN1 and WIN1252 can represent at most 256 characters (0-255), while UTF-8 is a variable-length encoding that can represent over 1 million characters. Some characters, especially international characters (accented letters, CJK characters, symbols), exist in UTF-8 but not in single-byte encodings. The byte sequence shown in the error (e.g., 0xEF 0xBC 0x8C) is the UTF-8 representation of a character that has no equivalent in the target encoding.

PostgreSQL Encoding Conversion Tables: PostgreSQL uses built-in conversion tables for supported encoding pairs. Some character mappings are ambiguous or lossy—for example, a curly quote might map to a straight quote or be lost entirely. If you need to convert between unsupported encoding pairs, you may need to use system tools like iconv.

Unicode Normalization: UTF-8 can represent the same character in multiple ways (composed vs. decomposed). PostgreSQL may struggle converting decomposed UTF-8 sequences (e.g., separate combining characters) to other encodings. In such cases, normalize the data first: SELECT NFC(column_name) (PostgreSQL 13+).

Connection Pool Encoding: In multi-threaded or connection pool scenarios, ensure encoding is set per-connection, not globally. Some frameworks cache connections, so encoding mismatches can propagate. Force re-initialization of the connection pool after changing encoding settings.

Frequently asked questions

What does "22P05" mean?
Error 22P05 is a character encoding conversion error that occurs when PostgreSQL attempts to translate a character from one encoding to another and the target encoding has no valid representation for that character. This commonly happens when the database uses UTF-8 (which supports many international characters) but the client application or ODBC driver expects a narrower encoding like WIN1252 (Windows Latin-1) or LATIN1 (ISO 8859-1). For example, a "fullwidth comma" character (Unicode U+FF0C) exists in UTF-8 but has no equivalent in WIN1252, causing the conversion to fail.
How do I fix "22P05"?
PostgreSQL error 22P05 occurs when a character cannot be converted between database encodings, typically when moving data from UTF-8 to a single-byte encoding like WIN1252 or LATIN1. Resolve this by switching to Unicode drivers, fixing invalid characters, or adjusting client-side encoding settings.
What causes "22P05"?
Using ODBC ANSI driver instead of Unicode driver when connecting to UTF-8 database Database migration from multi-byte encoding (UTF-8) to single-byte encoding (LATIN1, WIN1252) without character validation Client application or connection string specifies incompatible character set encoding
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