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
includedwith 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