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