Message-Id: <5.1.0.14.0.20030218224724.02705ff8@localhost>
Date: Tue, 18 Feb 2003 23:13:04 +0000
To: pgsql-sql@postgresql.org
From: JBJ <postgre@totw.org>
Subject: once again, sorting with Unicode
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable

Thus is the nature of my problem:

I have a multi-lingual database (currently 11 languages) which sorts fine=
=20
in MySQL (8859-1 character set)
I have now converted the data to Unicode and compiled Postgre with unicode=
=20
support.

I can select and insert unicode and so was rather pleased about that. Until=
=20
I saw that it wasn't working properly when ordering!

I have a table with all 11 languages containing a country list, the webuser=
=20
will be able (this is not live yet, MySQL is still running the live stuff=
=20
and handling it well) to switch between languages and so I select from a=20
different column (english, icelandic, russian etc) to display the country=
=20
list correctly and do ORDER BY that specific column. I noticed this problem=
=20
when the output was Icelandic, and have confirmed it with german and other=
=20
languages using non-English characters. The sorting goes all wrong.

All the special letters come in between the A, so we have =DE A =DA A =CD A=
 =D3 et=20
cetera. This is wrong, =DE should be the third last letter in the row for=
=20
example, not the first and A should be in a right order.

I have is_IS as all the LC_X and so am even more surprised by this.=20
Furthermore I will have to be able to switch between is_IS and others to=20
get the correct sort order ( russian sort order different from icelandic=20
for example, simply due to the nature of the letters) live when the user=20
selects a different language.

Example is here: http://www.worldfootball.org/sort-test.php
-notice how countries beginning with A are surrounded by various others=20
starting with non-english letters in the Icelandic section
-in the German section you can see that =D6sterreich comes in between the A=
=20
countries

I'm sure this is a simple matter but my googling for the last 3 days has=20
proved unsuccessful so far.

Feedback greatly appreciated

regards,
JBJ