Re: Collate order on Mac OS X, text with diacritics in UTF-8

Am 12.01.2010 um 12:36 schrieb Martin Flahault:

> We have spend some time evaluating PostgreSQL and we can't get correct outputs with the ORDER BY command.
> LC_COLLATE and LC_CTYPE are set to fr_FR.UTF-8.
>
> It seems there is a known problem with the collating order of text including diacritics with the UTF8 encodings on
BSDsystems. 
>
> Does anyone know a workaround ?

The best i've seen so far is:

CREATE OR REPLACE FUNCTION f_getorderbyfriendlyversion(texttoconvert text)
 RETURNS text AS
$BODY$
select translate(upper($1),'ÄÖÜ','AOU')--add french diacritical characters here
$BODY$
 LANGUAGE 'sql' IMMUTABLE STRICT
 COST 100;
ALTER FUNCTION f_getorderbyfriendlyversion(text) OWNER TO postgres;

CREATE INDEX idx_mytable_myfield_orderbyfriendly
 ON mytable
 USING btree
 (f_getorderbyfriendlyversion(myfield::text));

Select * from mytable order by f_getorderbyfriendlyversion(myfield);

Not an ideal solution, but it seems to perform quite well.

Best wishes from Berlin,

Maximilian Tyrtania