Re: Specifying column level collations

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