Обсуждение: Specifying column level collations

Hi,

I'm playing around with 9.1beta1 and would like to create a table where one column has a non-default collation.

But whatever I try, I can't find the correct name that I have to use.

My database is initialized as follows:

postgres=# select version();                            version
---------------------------------------------------------------- PostgreSQL 9.1beta1, compiled by Visual C++ build
1500,32-bit
 
(1 row)

postgres=# select name, setting
postgres-# from pg_settings
postgres-# where name in ('lc_collate', 'server_encoding', 'client_encoding');      name       |       setting
-----------------+--------------------- client_encoding | WIN1252 lc_collate      | German_Germany.1252 server_encoding
|UTF8
 
(3 rows)


Now I'm trying to create a table where one column's collation is set to french:

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
notexist
 

So, how do I specify the collation there?

And is there a command to show me all available collations that I can use?

Thanks
Thomas

On 05/07/2011 01:19 PM, Thomas Kellerer wrote:
> Hi,
> 
> I'm playing around with 9.1beta1 and would like to create a table where
> one column has a non-default collation.
> 
> But whatever I try, I can't find the correct name that I have to use.
> 
> My database is initialized as follows:
> 
> postgres=# select version();
>                             version
> ----------------------------------------------------------------
>  PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit
> (1 row)
> 
> postgres=# select name, setting
> postgres-# from pg_settings
> postgres-# where name in ('lc_collate', 'server_encoding',
> 'client_encoding');
>       name       |       setting
> -----------------+---------------------
>  client_encoding | WIN1252
>  lc_collate      | German_Germany.1252
>  server_encoding | UTF8
> (3 rows)
> 
> 
> Now I'm trying to create a table where one column's collation is set to
> french:
> 
> 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

Then, you'll be able to create your table:

b1=# CREATE TABLE foo (bar TEXT COLLATE fr);
CREATE TABLE
b1=# \d foo    Table "public.foo"Column | Type | Modifiers
--------+------+------------bar    | text | collate fr

> And is there a command to show me all available collations that I can use?
> 

b1=# select * from pg_collation; collname  | collnamespace | collowner | collencoding | collcollate |
collctype
------------+---------------+-----------+--------------+-------------+------------default    |            11 |
10|           -1 |             |C          |            11 |        10 |           -1 | C           | CPOSIX      |
      11 |        10 |           -1 | POSIX       | POSIXen_AG      |            11 |        10 |            6 | en_AG
    | en_AGen_AG.utf8 |            11 |        10 |            6 | en_AG.utf8  | [...]fr_FR      |            11 |
 10 |            6 | fr_FR.utf8  |
 
fr_FR.utf8fr_FR.utf8 |            11 |        10 |            6 | fr_FR.utf8  |
fr_FR.utf8fr_LU      |            11 |        10 |            6 | fr_LU.utf8  |
fr_LU.utf8fr_LU.utf8 |            11 |        10 |            6 | fr_LU.utf8  |
fr_LU.utf8ucs_basic  |            11 |        10 |            6 | C           | Cfr         |          2200 |        10
|           6 | fr_FR.UTF8  |
 
fr_FR.UTF8
(47 rows)

Or \dO (o in uppercase) inside psql:

b1=# \dO          List of collationsSchema | Name |  Collate   |   Ctype
--------+------+------------+------------public | fr   | fr_FR.UTF8 | fr_FR.UTF8
(1 row)


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

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

Thomas Kellerer <spam_eater@gmx.net> writes:
> My database is initialized as follows:

> postgres=# select version();
>                              version
> ----------------------------------------------------------------
>   PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit
> (1 row)

I gather this is on Windows.

Windows has its own notion of locale names, which look like this:

>   lc_collate      | German_Germany.1252

rather than the "de_DE" type of convention that's used by every other
platform on the planet.  There is not yet support in initdb for
pre-populating pg_collation with Windows-style entries, so you will
have to create your own entries.  Presumably this would work for you,
for instance:

CREATE COLLATION german (locale='German_Germany.1252');

I don't know how to find out exactly what locale names are recognized by
Windows, so can't help you much further than that.
        regards, tom lane

Tom Lane wrote on 07.05.2011 18:48:
> Thomas Kellerer<spam_eater@gmx.net>  writes:
>> My database is initialized as follows:
>
>> postgres=# select version();
>>                               version
>> ----------------------------------------------------------------
>>    PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit
>> (1 row)
>
> I gather this is on Windows.
>
> Windows has its own notion of locale names, which look like this:
>
>>    lc_collate      | German_Germany.1252
>
> rather than the "de_DE" type of convention that's used by every other
> platform on the planet.  There is not yet support in initdb for
> pre-populating pg_collation with Windows-style entries, so you will
> have to create your own entries.  Presumably this would work for you,
> for instance:
>
> CREATE COLLATION german (locale='German_Germany.1252');
>

Ah! That did it. Thanks a lot.

Regards
Thomas