Add a new collation to a Postgres database

I have these collations:

postgres=# select * from pg_collation;
collname | collnamespace | collowner | collencoding | collcollate | collctype
------------+---------------+-----------+--------------+-------------+-----------
default | 11 | 10 | -1 | |
C | 11 | 10 | -1 | C | C
POSIX | 11 | 10 | -1 | POSIX | POSIX
en_US | 11 | 10 | 6 | en_US.utf8 | en_US.utf8
en_US.utf8 | 11 | 10 | 6 | en_US.utf8 | en_US.utf8
ucs_basic | 11 | 10 | 6 | C | C
(6 rows)

I installed a new locale on my system and I'd like to use it on postgres. It seems that the only way to install a new collate is using the initdb command, but it requires to make a pg_dumpall, delete the postgres data directory, run initdb and restore all the data from the dump. Isn't there an easier solution?

You don't need to create a new DB cluster. You can use CREATE COLLATION in Postgres 9.1 or later. Example in the manual:


To create a collation from the operating system locale fr_FR.utf8
(assuming the current database encoding is UTF8):

CREATE COLLATION french (LOCALE = 'fr_FR.utf8');

Be sure to read the chapter Managing Collations in the manual to understand the details. In particular:


Within any particular database, only collations that use that
database's encoding are of interest. Other entries in pg_collation are ignored.

I.e., the collation has to match your database encoding to be available.

But there's more. Read it.

StackExchange Database Administrators Q#105147, answer score: 8