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.