Message-ID: <4DC534E1.40801@lelarge.info>
Date: Sat, 07 May 2011 14:02:41 +0200
From: Guillaume Lelarge <guillaume@lelarge.info>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.17) Gecko/20110424 Thunderbird/3.1.10
MIME-Version: 1.0
To: Thomas Kellerer <spam_eater@gmx.net>
CC: pgsql-sql@postgresql.org
Subject: Re: Specifying column level collations
References: <iq39rc$8sl$1@dough.gmane.org>
In-Reply-To: <iq39rc$8sl$1@dough.gmane.org>
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit

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           | C
 POSIX      |            11 |        10 |           -1 | POSIX       | POSIX
 en_AG      |            11 |        10 |            6 | en_AG       | en_AG
 en_AG.utf8 |            11 |        10 |            6 | en_AG.utf8  | [...]
 fr_FR      |            11 |        10 |            6 | fr_FR.utf8  |
fr_FR.utf8
 fr_FR.utf8 |            11 |        10 |            6 | fr_FR.utf8  |
fr_FR.utf8
 fr_LU      |            11 |        10 |            6 | fr_LU.utf8  |
fr_LU.utf8
 fr_LU.utf8 |            11 |        10 |            6 | fr_LU.utf8  |
fr_LU.utf8
 ucs_basic  |            11 |        10 |            6 | C           | C
 fr         |          2200 |        10 |            6 | fr_FR.UTF8  |
fr_FR.UTF8
(47 rows)

Or \dO (o in uppercase) inside psql:

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


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com