Re: Specifying column level collations

Guillaume Lelarge wrote on 07.05.2011 14:02:
>> create table foo (bar text collate "fr_FR") --> collation "fr_FR" for
>> encoding "UTF8" does not exist
>> create table foo (bar text collate "fr_FR.1252") --> collation "fr_FR"
>> for encoding "UTF8" does not exist
>> create table foo (bar text collate "fr_FR.UTF8") --> collation "fr_FR"
>> for encoding "UTF8" does not exist
>> create table foo (bar text collate "French_France.1252") --> collation
>> "French_France.1252" for encoding "UTF8" does not exist
>>
>> So, how do I specify the collation there?
>>
>
> You first need to use "CREATE COLLATION", such as:
>
> b1=# CREATE COLLATION fr (locale='fr_FR');
> CREATE COLLATION
>

Thanks for the quick answer.

It seems there is something missing with my installation:

postgres=# CREATE COLLATION fr (locale='fr_FR');
ERROR: could not create locale "fr_FR": No such file or directory

I used the ZIP distribution from EnterpriseDB (not the installer) so maybe the support for collations is simply not included with the "plain" binaries.

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
(3 rows)

Regards
Thomas