pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSpecifying column level collations
5+ messages / 3 participants
[nested] [flat]
* Specifying column level collations
@ 2011-05-07 11:19 Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 2 replies; 5+ messages in thread
From: Thomas Kellerer @ 2011-05-07 11:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Specifying column level collations
@ 2011-05-07 12:02 Guillaume Lelarge <guillaume@lelarge.info>
parent: Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 1 reply; 5+ messages in thread
From: Guillaume Lelarge @ 2011-05-07 12:02 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: 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 | 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Specifying column level collations
@ 2011-05-07 12:16 Thomas Kellerer <spam_eater@gmx.net>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas Kellerer @ 2011-05-07 12:16 UTC (permalink / raw)
To: pgsql-sql
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 included with 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Specifying column level collations
@ 2011-05-07 16:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Thomas Kellerer <spam_eater@gmx.net>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2011-05-07 16:48 UTC (permalink / raw)
To: Thomas Kellerer <spam_eater@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Specifying column level collations
@ 2011-05-07 20:12 Thomas Kellerer <spam_eater@gmx.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Thomas Kellerer @ 2011-05-07 20:12 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-05-07 20:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-07 11:19 Specifying column level collations Thomas Kellerer <spam_eater@gmx.net>
2011-05-07 12:02 ` Guillaume Lelarge <guillaume@lelarge.info>
2011-05-07 12:16 ` Thomas Kellerer <spam_eater@gmx.net>
2011-05-07 16:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-07 20:12 ` Thomas Kellerer <spam_eater@gmx.net>
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