Re: Specifying column level collations - Mailing list pgsql-sql
From | Guillaume Lelarge |
---|---|
Subject | Re: Specifying column level collations |
Date | |
Msg-id | 4DC534E1.40801@lelarge.info Whole thread Raw |
In response to | Specifying column level collations (Thomas Kellerer <spam_eater@gmx.net>) |
Responses |
Re: Specifying column level collations
|
List | pgsql-sql |
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