6

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?

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.