To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Specifying column level collations
Date: Sat, 07 May 2011 13:19:10 +0200
Lines: 38
Message-ID: <iq39rc$8sl$1@dough.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666

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?

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

Thanks
Thomas