Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

CREATE DATABASE with UTF8 fails because the inherited locale needs LATIN1

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

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18, Ubuntu 24.04
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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;"
  2. 2Match what you see, then jump to its fix

    1. 1template1 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. 2The 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. 3template0, 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

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 mydb

3Verify

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

Did this fix it?

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 utf

2Fix

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

Did this fix it?

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

3Verify

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.