Add a new collation to a Postgres database

Question

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?

Answer 1

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.