From: | BGoebel <b(dot)goebel(at)prisma-computer(dot)de> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: german sort is wrong |
Date: | 2012-03-23 18:04:57 |
Message-ID: | 1332525897349-5590321.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Haifeng Liu | 2012-03-26 05:39:01 | Daily Rotated Insertion |
Previous Message | Raghavendra | 2012-03-23 15:39:22 | Re: WAL archiving question |