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:. >> >> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
