Re: Collation in ORDER BY not lexicographical

am 29.09.2009 11:21 Uhr schrieb Scott Marlowe unter scott(dot)marlowe(at)gmail(dot)com:

> On Tue, Sep 29, 2009 at 2:52 AM, Paul Gaspar <devlist(at)revolversoft(dot)com> wrote:
>> Hi!
>>
>> We have big problems with collation in ORDER BY, which happens in binary
>> order, not alphabetic (lexicographical), like:.
>>
>> A
>> B
>> Z
>> a
>> z
>> Ä
>> Ö
>> ä
>> ö
>>
>
>> 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