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?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
CREATE DATABASE statement in the question will copy the new database from
template1, the default template database, whose
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
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.