How to fix Encoding mismatch in PostgreSQL

PostgreSQLINTERMEDIATEHIGH

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 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.

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';

How to fix Encoding mismatch in PostgreSQL

What this error means

Typical symptoms

Common causes

How to fix "Encoding mismatch"

Advanced notes

Related errors

Official resources & further reading