I am trying to set the collation for a new database in PostgreSQL 13 but it does not seem to take effect:

postgres=# \l
                                      List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
 assets    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

How can I have the new database reflect my collation changes?

  • That cannot happen with PostgreSQL, you'd get an error message... Apr 19, 2022 at 6:40
  • @LaurenzAlbe Right, I'm not sure why my system did not notify me of this. Bizarre...
    – leetbacoon
    Apr 19, 2022 at 16:53

1 Answer 1


The CREATE DATABASE statement in the question will copy the new database from template1, the default template database, whose LC_COLLATE is en_US.UTF-8. Since the new database wants a C collation, normally the database creation should fail with this error:


ERROR:  new collation (C) is incompatible with the collation of the template database (en_US.UTF-8)
HINT:  Use the same collation as in the template database, or use template0 as template.

This is because Postgres doesn't know whether this template1 database contains objects (indexes, mostly) that rely on en_US.UTF-8 string ordering. If it blindly copied it into new database with the C collation, these indexes would be corrupted.

Aside from the fact that you didn't get that error message (which seems really weird) , the advice in the HINT section of the error is what you need: add TEMPLATE 'template0' to the options of CREATE DATABASE. template0, contrary to template1, cannot be populated with custom contents, so it's guaranteed to contain only data that is compatible with any collation and encoding supported by Postgres.

  • Thank you for the detailed answer. So, with using template0 and the same options mentioned in my question, will I be able to also store data containing characters from other languages? (e.g. معلومات مثال and ダミーデータ) This is necessary to have in my database. I still want them all to use C collation.
    – leetbacoon
    Apr 18, 2022 at 20:26
  • 1
    @leetbacoon: yes. The ability to store characters from all languages comes with the UTF8 encoding, and it's compatible with the contents of template0. Apr 19, 2022 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.