Thank you all very much for your help.
Maximilian, we simplified your replacing code:
replace(replace(replace(replace(replace(replace
($1,'Ä','A'),'Ö','O'),'Ü','U'
),'ä','a'),'ö','o'),'ü','u');
to this:
translate(upper($1),'ÄÖÜ','AOU')
Paul
Am 29.09.2009 um 14:36 schrieb Maximilian Tyrtania:
am 29.09.2009 11:21 Uhr schrieb Scott Marlowe unter scott.marlowe@xxxxxxxxx
:
On Tue, Sep 29, 2009 at 2:52 AM, Paul Gaspar <devlist@xxxxxxxxxxxxxxxx
> wrote:
Hi!
We have big problems with collation in ORDER BY, which happens in
binary
order, not alphabetic (lexicographical), like:.
PG is running on Mac OS X 10.5 and 10.6 Intel.
I seem to recall there were some problem with Mac locales at some
point being broken. Could be you're running into that issue.
Yep, i ran into this as well. Here is my workaround: Create a
function like
this:
CREATE OR REPLACE FUNCTION f_getorderbyfriendlyversion(texttoconvert
text)
RETURNS text AS
$BODY$
select
replace(replace(replace(replace(replace(replace
($1,'Ä','A'),'Ö','O'),'Ü','U'
),'ä','a'),'ö','o'),'ü','u');
$BODY$
LANGUAGE 'sql' IMMUTABLE STRICT
COST 100;
ALTER FUNCTION f_getorderbyfriendlyversion(text) OWNER TO postgres;
Then create an index like this:
create index idx_personen_nachname_orderByFriendly on personen
(f_getorderbyfriendlyversion(nachname))
Now you can do:
select * from personen order by f_getorderbyfriendlyversion
(p.nachname)
Seems pretty fast.
Best,
Maximilian Tyrtania
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
