How to fix Database locale is incompatible with character set in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 29, 2025· 192 views

This error occurs when a PostgreSQL database's character encoding does not match the LC_CTYPE and LC_COLLATE locale settings. PostgreSQL requires that database encodings are compatible with the system locale. For non-C/POSIX locales, only one character set will work correctly with each locale.

Quick fix

Fixes Database locale is incompatible with character set

locale -a
  1. Check available locales on your system
  2. Install missing locale (Linux)
  3. Create database with matching encoding and locale
  4. Use C or POSIX locale as a fallback
postgresql — error
locale -a
Database locale is incompatible with character set

What this error means

PostgreSQL enforces a strict compatibility requirement between database character sets and locale settings. Each database has an LC_CTYPE (character classification) and LC_COLLATE (sort order) locale setting that determines which character set is allowed. For C or POSIX locale, any character set is acceptable, but for other system locales (like en_US.UTF-8), only a matching character set is permitted. When you attempt to create a database with a character encoding that is incompatible with its locale, PostgreSQL raises this error. This typically happens during database creation with CREATE DATABASE or when initializing a new cluster with initdb.

Typical symptoms

  • Database creation fails with character set incompatibility error
  • CREATE DATABASE command rejects the specified encoding for the locale
  • PostgreSQL cluster initialization (initdb) fails during setup
  • Database restore/migration fails when target encoding does not match locale
  • Locale validation errors appear in PostgreSQL logs during database initialization

Common causes

  1. Attempting to create a database with UTF-8 encoding using a non-UTF-8 compatible locale
  2. Specifying an LC_CTYPE or LC_COLLATE locale that does not support the desired character set
  3. Creating a database with LATIN1 encoding while using a UTF-8 system locale
  4. System locale is not installed or is misconfigured on the server
  5. Migrating a database from one server where encoding and locale match to another where they do not
  6. Using an incorrect locale name that is not available on the system

How to fix "Database locale is incompatible with character set"

1Check available locales on your system

First, determine which locales are installed on your operating system:

bash
locale -a

This lists all available locales. Look for the locale you want to use (e.g., en_US.UTF-8, C, POSIX). If your desired locale is not listed, you will need to install it.

2Install missing locale (Linux)

If your desired locale is not available, install it:

Ubuntu/Debian:

bash
sudo locale-gen en_US.UTF-8
sudo update-locale

CentOS/RHEL/Fedora:

bash
sudo localedef -i en_US -f UTF-8 en_US.UTF-8

After installation, verify the locale is available:

bash
locale -a | grep en_US
3Create database with matching encoding and locale

When creating a new database, explicitly specify both the encoding and locale settings to ensure compatibility:

sql
CREATE DATABASE my_database
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8';

The encoding must match the character set implied by the locale. For UTF-8 locales, use ENCODING 'UTF8'. For LATIN1 locales, use ENCODING 'LATIN1'.

To verify the database was created with correct settings:

sql
SELECT datname, encoding, datcollate, datctype FROM pg_database WHERE datname = 'my_database';
4Use C or POSIX locale as a fallback

If you cannot resolve the locale compatibility issue, use the C or POSIX locale, which accepts any character set:

sql
CREATE DATABASE my_database
  ENCODING 'UTF8'
  LC_COLLATE 'C'
  LC_CTYPE 'C';

Note: C/POSIX locale uses byte-by-byte sorting and does not perform locale-aware operations like case-insensitive matching. For production systems, this is a workaround only.

5If using PostgreSQL with ICU provider

PostgreSQL 10+ supports ICU (International Components for Unicode) as an alternative to OS-provided locales. ICU is independent of the operating system locale:

sql
CREATE DATABASE my_database
  ENCODING 'UTF8'
  LOCALE_PROVIDER 'icu'
  LOCALE 'en_US';

ICU provides consistent collation and character classification across platforms. Check if your PostgreSQL installation supports ICU:

sql
SHOW icu_validation_level;

If ICU is available and you have control over database creation, this is the recommended solution.

6Restore database with correct locale settings

If you are restoring a database dump (pg_dump), you may need to recreate the database with matching locale:

bash
# Drop existing database if necessary
psql -U postgres -c "DROP DATABASE IF EXISTS my_database;"

# Recreate with correct settings
psql -U postgres -c "CREATE DATABASE my_database
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8';"

# Restore the dump
psql -U postgres -d my_database < my_database.sql

Alternatively, use pg_restore with the -C flag to recreate the database:

bash
pg_restore -C -d postgres my_database.dump

Advanced notes

PostgreSQL stores locale settings at database creation and they cannot be changed without recreating the database. LC_COLLATE and LC_CTYPE affect string sorting, case conversion, and character classification throughout the database. If you change the system locale after PostgreSQL cluster initialization, existing databases retain their original locale settings. For multi-language support or consistency across platforms, ICU provider (PostgreSQL 10+) is preferred over OS locales because it does not depend on system locale availability. When migrating databases between servers with different locale configurations, always explicitly specify the target encoding and locale to avoid incompatibility. Test character set and locale compatibility in a staging environment before applying to production.

Frequently asked questions

What does "Database locale is incompatible with character set" mean?
PostgreSQL enforces a strict compatibility requirement between database character sets and locale settings. Each database has an LC_CTYPE (character classification) and LC_COLLATE (sort order) locale setting that determines which character set is allowed. UTF-8), only a matching character set is permitted.
How do I fix "Database locale is incompatible with character set"?
This error occurs when a PostgreSQL database's character encoding does not match the LC_CTYPE and LC_COLLATE locale settings. PostgreSQL requires that database encodings are compatible with the system locale. For non-C/POSIX locales, only one character set will work correctly with each locale.
What causes "Database locale is incompatible with character set"?
Attempting to create a database with UTF-8 encoding using a non-UTF-8 compatible locale Specifying an LC_CTYPE or LC_COLLATE locale that does not support the desired character set Creating a database with LATIN1 encoding while using a UTF-8 system locale
Is the "Database locale is incompatible with character set" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading