Collation won't set when creating new database

All we need is an easy explanation of the problem, so here it is.

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

postgres=# CREATE DATABASE assets ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'en_US.UTF-8';
CREATE DATABASE
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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

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

CREATE DATABASE assets ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'en_US.UTF-8';

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply