From: | Maximilian Tyrtania <maximilian(dot)tyrtania(at)onlinehome(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Collate order on Mac OS X, text with diacritics in UTF-8 |
Date: | 2010-01-13 10:21:24 |
Message-ID: | D8CCFE1E-6E1E-420C-8156-47BD61C66CC0@onlinehome.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Ralf Schuchardt | 2010-01-13 10:25:53 | What happens when you kill the postmaster? |
Previous Message | Daniel Schuchardt | 2010-01-13 10:15:54 | postgresql 8.1 windows 2008 64 bit |