CREATE DATABASE with UTF8 fails because the inherited locale needs LATIN1
ERROR: encoding "UTF8" does not match locale "en_US"Short answer
A new database copies template1, and template1 here carries an en_US locale that belongs to LATIN1. UTF8 does not fit that locale, so PostgreSQL refuses. Create the database from template0 and name a UTF-8 locale yourself, for example en_US.UTF-8, or use the C locale, which accepts every encoding.
Fix it fast
1Find out which cause you have
Look at how the template databases and your other databases were created. The datcollate and datctype columns hold the locale; the second column is the encoding.
psql -d postgres -c "SELECT datname, pg_encoding_to_char(encoding) AS encoding, datcollate, datctype FROM pg_database ORDER BY datname;"2Match what you see, then jump to its fix
- 1
template1 shows LATIN1 with en_US in datcollate and datctype, and your CREATE DATABASE asks for UTF8The new database inherits a non-UTF-8 locale from template1Fix - 2
The command from the first cause fails, or locale -a does not print the name you typedThe UTF-8 locale name is not one the operating system hasFix - 3
template0, template1 and your existing databases all show LATIN1 or an en_US locale without .UTF-8The whole cluster was created under a non-UTF-8 localeFix
- 1
What does the error look like?
ERROR: encoding "UTF8" does not match locale "en_US"DETAIL: The chosen LC_CTYPE setting requires encoding "LATIN1".DETAIL: The chosen LC_COLLATE setting requires encoding "LATIN1".ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (LATIN1)
Cause 1 of 3
The new database inherits a non-UTF-8 locale from template1
1Check
Without options, CREATE DATABASE copies template1, and encoding and locale must match those of the template unless the template is template0. The query above shows what template1 holds.
psql -d postgres -c "SELECT datname, pg_encoding_to_char(encoding), datcollate FROM pg_database WHERE datname IN ('template0', 'template1');"2Fix
Copy template0 instead, and give the encoding and a UTF-8 locale in the same command. A locale and an encoding that do not belong together produce this error again.
CREATE DATABASE mydb
ENCODING 'UTF8'
LC_COLLATE 'en_US.UTF-8'
LC_CTYPE 'en_US.UTF-8'
TEMPLATE template0;
-- the same from the shell
-- createdb -E UTF8 -T template0 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 mydb3Verify
The new database appears with UTF8 and the UTF-8 locale.
psql -d postgres -c "SELECT datname, pg_encoding_to_char(encoding), datcollate, datctype FROM pg_database WHERE datname = 'mydb';"Confirmed by postgresql.org
Cause 2 of 3
The UTF-8 locale name is not one the operating system has
1Check
On most Unix systems locale -a lists the locales that exist. Type the name exactly as it appears there. The PostgreSQL manual itself shows both spellings, such as sv_SE.utf8 and fr_BE.UTF-8.
locale -a | grep -i utf2Fix
Use a name from that list. If no UTF-8 locale you need is installed, add it with your operating system's locale tools and retry. If you do not need language-aware sorting, the C locale allows every encoding, including UTF8.
CREATE DATABASE mydb
ENCODING 'UTF8'
LC_COLLATE 'C'
LC_CTYPE 'C'
TEMPLATE template0;3Verify
CREATE DATABASE completes and the database shows UTF8.
psql -d postgres -c "SELECT datname, pg_encoding_to_char(encoding) FROM pg_database WHERE datname = 'mydb';"Confirmed by postgresql.org
Cause 3 of 3
The whole cluster was created under a non-UTF-8 locale
1Check
initdb takes the locale of the environment it runs in unless told otherwise, and picks the encoding from it. LC_COLLATE and LC_CTYPE cannot be changed on an existing database, so a database in the wrong locale has to be recreated.
echo "LC_ALL=$LC_ALL LANG=$LANG"
psql -d postgres -c "SELECT datname, pg_encoding_to_char(encoding), datctype FROM pg_database ORDER BY datname;"2Fix
For a few databases, create each from template0 as in the first cause and load the old data into it. To make UTF-8 the default for the whole server, initialise a new cluster with the encoding and locale set, then dump and load into it.
# new cluster, UTF-8 by default
initdb -D /var/lib/postgresql/new-data -E UTF8 --locale=en_US.UTF-8
# move a database: dump it, create the target from template0, load
pg_dump -f old.sql olddb
createdb -E UTF8 -T template0 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 newdb
psql -X -d newdb -f old.sql3Verify
Every database you moved reports UTF8, and the application reads accented text correctly.
psql -d postgres -c "SELECT datname, pg_encoding_to_char(encoding), datcollate FROM pg_database ORDER BY datname;"Confirmed by postgresql.org
How do I stop it coming back?
- Set the locale and encoding when you create a cluster (initdb -E UTF8 --locale=en_US.UTF-8) instead of inheriting them from whatever shell ran it.
- Put ENCODING, a matching locale and TEMPLATE template0 in every CREATE DATABASE script, so it works the same on every server.
- Check locale -a on a new server before running scripts that name a locale.
- Run initdb and database-creation scripts from a shell where LANG is a UTF-8 locale, so any default you do rely on is UTF-8.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
invalid byte sequence for encoding UTF8 while importing data
CONTEXT: COPY customers, line 12
PostgreSQL checks incoming text against the encoding the client declares.
2 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
Django cannot connect to PostgreSQL: connection refused on port 5432, locally or in Docker
Is the server running on that host and accepting TCP/IP connections?
Connection refused means nothing accepted the connection at the host and port Django used.
4 causes, each with a fixDatabases
manage.py test stops with 'permission denied to create database' on PostgreSQL
django.db.utils.ProgrammingError: permission denied to create database
Django creates a separate database called test_ plus your database name, using the DATABASES USER.
2 causes, each with a fixDatabases
An EC2 instance cannot reach its RDS database
Could not open connection to the host, on port <port>: Connect failed
RDS denies all access until a security group allows it.
3 causes, each with a fixDatabases
IntelliJ IDEA Test Connection fails for a PostgreSQL data source
Connection to localhost:5432 refused.
When Test Connection fails, work from the bottom up.
4 causes, each with a fix