pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedgerman sort is wrong
6+ messages / 3 participants
[nested] [flat]
* german sort is wrong
@ 2012-03-20 15:13 Reinhard Asmus <reinhard.asmus@spdfraktion.de>
0 siblings, 1 reply; 6+ messages in thread
From: Reinhard Asmus @ 2012-03-20 15:13 UTC (permalink / raw)
To: pgsql-admin
Hallo,
i make initdb with --locale='de_DE.utf8'.
the result is:
The database cluster will be initialized with locale de_DE.utf8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "german".
when i make a sort this is the result:
select * from sgvs.test order by bezeichnung;
id | bezeichnung | schlagworte
----+-------------+-------------
11 | Anton |
8 | Ätna |
10 | Erna |
2 | Kammer |
6 | Kanten |
1 | Käse |
4 | Köbern |
3 | Kümmern |
5 | Kuno |
9 | Möbel |
7 | Zuber |
in german this is wrong. what is the problem?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: german sort is wrong
@ 2012-03-21 13:51 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Reinhard Asmus <reinhard.asmus@spdfraktion.de>
0 siblings, 2 replies; 6+ messages in thread
From: Kevin Grittner @ 2012-03-21 13:51 UTC (permalink / raw)
To: pgsql-admin; Reinhard Asmus <reinhard.asmus@spdfraktion.de>
Reinhard Asmus <reinhard.asmus@spdfraktion.de> wrote:
> when i make a sort this is the result:
> [vowel with umlaut sorts equal to vowel without]
> in german this is wrong. what is the problem?
It appears to be one of three different "right" ways:
http://en.wikipedia.org/wiki/German_alphabet#Sorting
Is there a different collation available on your OS to sort names?
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: german sort is wrong
@ 2012-03-21 14:21 Reinhard Asmus <reinhard.asmus@spdfraktion.de>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 6+ messages in thread
From: Reinhard Asmus @ 2012-03-21 14:21 UTC (permalink / raw)
To: pgsql-admin
Am 21.03.2012 14:51, schrieb Kevin Grittner:
> Reinhard Asmus<reinhard.asmus@spdfraktion.de> wrote:
>
>> when i make a sort this is the result:
>
>> [vowel with umlaut sorts equal to vowel without]
>
>> in german this is wrong. what is the problem?
>
> It appears to be one of three different "right" ways:
>
> http://en.wikipedia.org/wiki/German_alphabet#Sorting
>
> Is there a different collation available on your OS to sort names?
>
> -Kevin
>
when i make the same thing in oracle i've got
Ätna
Anton
....
is it possible to get the same with postgresql and when how?
Reinhard
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: german sort is wrong
@ 2012-03-22 10:24 Reinhard Asmus <reinhard.asmus@spdfraktion.de>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 6+ messages in thread
From: Reinhard Asmus @ 2012-03-22 10:24 UTC (permalink / raw)
To: pgsql-admin
Am 21.03.2012 14:51, schrieb Kevin Grittner:
> Reinhard Asmus<reinhard.asmus@spdfraktion.de> wrote:
>
>> when i make a sort this is the result:
>
>> [vowel with umlaut sorts equal to vowel without]
>
>> in german this is wrong. what is the problem?
>
> It appears to be one of three different "right" ways:
>
> http://en.wikipedia.org/wiki/German_alphabet#Sorting
>
> Is there a different collation available on your OS to sort names?
>
> -Kevin
>
when i make the same thing in oracle i've got
Ätna
Anton
....
is it possible to get the same with postgresql and when how?
Reinhard
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: german sort is wrong
@ 2012-03-22 15:39 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Reinhard Asmus <reinhard.asmus@spdfraktion.de>
0 siblings, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2012-03-22 15:39 UTC (permalink / raw)
To: pgsql-admin; Reinhard Asmus <reinhard.asmus@spdfraktion.de>
Reinhard Asmus <reinhard.asmus@spdfraktion.de> wrote:
> Am 21.03.2012 14:51, schrieb Kevin Grittner:
>> Reinhard Asmus<reinhard.asmus@spdfraktion.de> wrote:
>>
>>> when i make a sort this is the result:
>>
>>> [vowel with umlaut sorts equal to vowel without]
>>
>>> in german this is wrong. what is the problem?
>>
>> It appears to be one of three different "right" ways:
>>
>> http://en.wikipedia.org/wiki/German_alphabet#Sorting
>>
>> Is there a different collation available on your OS to sort
>> names?
> when i make the same thing in oracle i've got
>
> Ätna
> Anton
> ....
>
> is it possible to get the same with postgresql and when how?
PostgreSQL doesn't implement collations itself; it can only use
collations available from your OS. It appears that your OS is
defaulting to the dictionary collation and you would prefer the
phone book collation. The Wikipedia link mentions that Windows
provides both collations; I suspect it's not the only OS that does,
but have no direct knowledge about that.
Starting in version 9.1 PostgreSQL can support collation overrides,
for example at the column level. Provided that your OS provides
both, you could use one for your default collation and override that
for specific columns, which sounds like it might make sense for
German.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: german sort is wrong
@ 2012-03-23 18:04 BGoebel <b.goebel@prisma-computer.de>
parent: Reinhard Asmus <reinhard.asmus@spdfraktion.de>
0 siblings, 0 replies; 6+ messages in thread
From: BGoebel @ 2012-03-23 18:04 UTC (permalink / raw)
To: pgsql-admin
Reinhard Asmus wrote
>
> when i make the same thing in oracle i've got
>
> Ätna
> Anton
> ....
>
> is it possible to get the same with postgresql and when how?
>
>
PostgreSQLs collation support is not complete. Until now, you can not define
a sort order of your own.
Workaround:
Step1: Define a function like this
CREATE OR REPLACE FUNCTION germanorder(text)
RETURNS text AS
$BODY$ SELECT
REPLACE(REPLACE(REPLACE(REPLACE(lower($1),'ß','ss'),'ä','ae'),'ö','oe'),'ü','ue')
$BODY$
LANGUAGE sql VOLATILE
COST 100;
ALTER FUNCTION prisorder(text)
OWNER TO postgres;
step2: Create an functional index
( http://www.postgresql.org/docs/7.3/static/indexes-functional.html )
CREATE INDEX bezeichnung_germanorder_idx ON test1 (germanorder(col1));
step3: Select using the function
select * from sgvs.test order by germanorder(bezeichnung);
and you will get what you want...
If speed does not matter you can omit step2.
regards
--
View this message in context: http://postgresql.1045698.n5.nabble.com/german-sort-is-wrong-tp5582836p5590321.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-03-23 18:04 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-03-20 15:13 german sort is wrong Reinhard Asmus <reinhard.asmus@spdfraktion.de>
2012-03-21 13:51 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-03-21 14:21 ` Reinhard Asmus <reinhard.asmus@spdfraktion.de>
2012-03-23 18:04 ` BGoebel <b.goebel@prisma-computer.de>
2012-03-22 10:24 ` Reinhard Asmus <reinhard.asmus@spdfraktion.de>
2012-03-22 15:39 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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