Skip site navigation (1) Skip section navigation (2)

Re: german sort is wrong

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 (view raw or flat)
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.

In response to

pgsql-admin by date

Next:From: Haifeng LiuDate: 2012-03-26 05:39:01
Subject: Daily Rotated Insertion
Previous:From: RaghavendraDate: 2012-03-23 15:39:22
Subject: Re: WAL archiving question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group