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

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

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.

If your desired locale is not available, install it:

Ubuntu/Debian:

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

CentOS/RHEL/Fedora:

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

After installation, verify the locale is available:

locale -a | grep en_US

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

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:

SELECT datname, encoding, datcollate, datctype FROM pg_database WHERE datname = 'my_database';

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

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.

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

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:

SHOW icu_validation_level;

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

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

# 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:

pg_restore -C -d postgres my_database.dump