Thread: Specifying column level collations
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
notexist
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
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
Guillaume Lelarge wrote on 07.05.2011 14:02:
>> 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
>
Thanks for the quick answer.
It seems there is something missing with my installation:
postgres=# CREATE COLLATION fr (locale='fr_FR');
ERROR: could not create locale "fr_FR": No such file or directory
I used the ZIP distribution from EnterpriseDB (not the installer) so maybe the support for collations is simply not
includedwith the "plain" binaries.
postgres=# 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
(3 rows)
Regards
Thomas
Thomas Kellerer <spam_eater@gmx.net> writes:
> My database is initialized as follows:
> postgres=# select version();
> version
> ----------------------------------------------------------------
> PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit
> (1 row)
I gather this is on Windows.
Windows has its own notion of locale names, which look like this:
> lc_collate | German_Germany.1252
rather than the "de_DE" type of convention that's used by every other
platform on the planet. There is not yet support in initdb for
pre-populating pg_collation with Windows-style entries, so you will
have to create your own entries. Presumably this would work for you,
for instance:
CREATE COLLATION german (locale='German_Germany.1252');
I don't know how to find out exactly what locale names are recognized by
Windows, so can't help you much further than that.
regards, tom lane
Tom Lane wrote on 07.05.2011 18:48: > Thomas Kellerer<spam_eater@gmx.net> writes: >> My database is initialized as follows: > >> postgres=# select version(); >> version >> ---------------------------------------------------------------- >> PostgreSQL 9.1beta1, compiled by Visual C++ build 1500, 32-bit >> (1 row) > > I gather this is on Windows. > > Windows has its own notion of locale names, which look like this: > >> lc_collate | German_Germany.1252 > > rather than the "de_DE" type of convention that's used by every other > platform on the planet. There is not yet support in initdb for > pre-populating pg_collation with Windows-style entries, so you will > have to create your own entries. Presumably this would work for you, > for instance: > > CREATE COLLATION german (locale='German_Germany.1252'); > Ah! That did it. Thanks a lot. Regards Thomas