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