From: Guillaume Lelarge <guillaume@lelarge.info>
To: Thomas Kellerer <spam_eater@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Specifying column level collations
Date: Sat, 07 May 2011 14:02:41 +0200
Message-ID: <4DC534E1.40801@lelarge.info> (raw)
In-Reply-To: <iq39rc$8sl$1@dough.gmane.org>
References: <iq39rc$8sl$1@dough.gmane.org>
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.frhttp://dalibo.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: guillaume@lelarge.info, spam_eater@gmx.net
Subject: Re: Specifying column level collations
In-Reply-To: <4DC534E1.40801@lelarge.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox