How to fix Encoding mismatch in PostgreSQL

PostgreSQLINTERMEDIATEHIGHUpdated Dec 29, 2025· 284 views

An encoding mismatch occurs when the server encoding, client encoding, or database collation settings do not align, preventing proper character handling and data transfer. Resolve by standardizing encodings to UTF-8 across your PostgreSQL configuration.

Quick fix

Fixes Encoding mismatch

SHOW server_encoding;
SHOW client_encoding;
SHOW lc_ctype;
SHOW lc_collate;
  1. Check the current server and client encodings
  2. Use UTF-8 encoding for all new databases
  3. Set the client encoding before operations
  4. Convert file encoding before import
postgresql — error
SHOW server_encoding;
SHOW client_encoding;
SHOW lc_ctype;
SHOW lc_collate;
Encoding mismatch

What this error means

PostgreSQL stores data in a specific character encoding (like UTF-8 or LATIN1) that is set when the database is created and cannot be changed afterwards. When the client (application or psql) uses a different encoding than the server, or when locale settings conflict with the database encoding, PostgreSQL cannot properly convert or validate characters. This causes errors during data insertion, retrieval, or migration. Common mismatches include incompatible character conversions, invalid byte sequences, and locale-encoding conflicts.

Typical symptoms

  • Database creation fails with "encoding does not match locale" error
  • Data import or COPY operations fail with "invalid byte sequence for encoding" errors
  • Query results contain corrupted characters or question marks (?) instead of expected text
  • Character conversion fails when using different client encoding than server encoding
  • Non-ASCII data cannot be stored or retrieved correctly
  • CSV or data migration imports fail when source encoding differs from database encoding

Common causes

  1. Database was created with SQL_ASCII or LATIN1 encoding while containing or expecting UTF-8 data
  2. Client encoding (PGCLIENTENCODING) differs from the server encoding
  3. Importing data from files with different encoding (e.g., ISO-8859-1 CSV into UTF-8 database)
  4. Locale settings (LC_CTYPE or LC_COLLATE) are incompatible with the chosen database encoding
  5. Character conversion functions are missing for the encoding pair being used
  6. Template database has different encoding than template0, causing inherited encoding conflicts

How to fix "Encoding mismatch"

1Check the current server and client encodings

First, verify what encodings are in use on your PostgreSQL instance:

sql
SHOW server_encoding;
SHOW client_encoding;
SHOW lc_ctype;
SHOW lc_collate;

For a specific database, query the system catalog:

sql
SELECT datname, pg_encoding_to_char(encoding), datcollate, datctype 
FROM pg_database 
WHERE datname = 'your_database_name';
2Use UTF-8 encoding for all new databases

When creating a new database, always specify UTF-8 encoding with compatible locales:

sql
CREATE DATABASE my_app_db
WITH ENCODING = 'UTF8'
LC_COLLATE = 'C.UTF-8'
LC_CTYPE = 'C.UTF-8'
TEMPLATE = template0;

UTF-8 is the modern standard and supports all Unicode characters. Using C.UTF-8 locale avoids locale-specific sorting issues.

3Set the client encoding before operations

If you must use a different client encoding, set it explicitly before importing or exporting data:

sql
SET client_encoding TO 'UTF8';

Or use the environment variable when connecting:

bash
export PGCLIENTENCODING=UTF8
psql -d your_database_name

In psql, you can also use:

bash
\encoding UTF8
4Convert file encoding before import

If importing from a file with different encoding, convert it first using iconv:

bash
# Convert from ISO-8859-1 to UTF-8
iconv -f ISO-8859-1 -t UTF-8 input_file.csv -o input_file_utf8.csv

Then import the converted file:

sql
COPY your_table (column1, column2) FROM STDIN WITH (FORMAT CSV, DELIMITER ',');
5Fix an existing database with encoding mismatch

If your database has the wrong encoding, you must dump and restore it:

bash
# Dump with explicit client encoding matching the actual data
pg_dump --client-encoding=ISO-8859-1 your_database_name > dump.sql

Edit dump.sql and change the client_encoding line to UTF-8:

sql
SET client_encoding = 'UTF8';

Then create a new database with correct encoding and restore:

bash
creatdb new_database_name --encoding UTF8 --lc-collate C.UTF-8 --lc-ctype C.UTF-8
psql new_database_name < dump.sql
6Fix template1 if it has incorrect encoding

If template1 has incorrect encoding and is causing new database creation to fail, you must rebuild it from template0:

sql
UPDATE pg_database SET datistemplate = false WHERE datname = 'template1';
DROP DATABASE template1;

CREATE DATABASE template1
WITH OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'C.UTF-8'
LC_CTYPE = 'C.UTF-8'
TEMPLATE = template0;

UPDATE pg_database SET datistemplate = true WHERE datname = 'template1';

Advanced notes

PostgreSQL supports multiple single-byte (ISO-8859 series) and multi-byte encodings (UTF-8, EUC_JP, SJIS). SQL_ASCII is a special "catch-all" encoding that accepts any byte sequence without validation; avoid it unless you have a specific reason and understand the data integrity risks. If you have data with mixed or corrupted encodings, you can use the convert() function to attempt conversion, or tools like pg_restore with the --no-owner and --no-privileges flags may help with problematic migrations. For international applications, UTF-8 is the universally recommended choice as it can represent all Unicode characters. Character conversion from one encoding to another is only possible if PostgreSQL has a defined conversion function for that pair; some uncommon encoding combinations may not be supported.

Frequently asked questions

What does "Encoding mismatch" mean?
PostgreSQL stores data in a specific character encoding (like UTF-8 or LATIN1) that is set when the database is created and cannot be changed afterwards. When the client (application or psql) uses a different encoding than the server, or when locale settings conflict with the database encoding, PostgreSQL cannot properly convert or validate characters. This causes errors during data insertion, retrieval, or migration.
How do I fix "Encoding mismatch"?
An encoding mismatch occurs when the server encoding, client encoding, or database collation settings do not align, preventing proper character handling and data transfer. Resolve by standardizing encodings to UTF-8 across your PostgreSQL configuration.
What causes "Encoding mismatch"?
Database was created with SQL_ASCII or LATIN1 encoding while containing or expecting UTF-8 data Client encoding (PGCLIENTENCODING) differs from the server encoding Importing data from files with different encoding (e.g., ISO-8859-1 CSV into UTF-8 database)
Is the "Encoding mismatch" 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