Re: Collation in ORDER BY not lexicographical

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(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:.
>>
>>> 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