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