From: Samuel Gendler <sgendler@ideasculptor.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Eyal Wilde <eyal@impactsoft.co.il>
Cc: pgsql-sql@postgresql.org
Subject: Re: a strange order by behavior
Date: Wed, 22 Jun 2011 03:15:19 -0700
Message-ID: <BANLkTi=YhCJ_0y83QS3e3buUNXL+7SwU6A@mail.gmail.com> (raw)
In-Reply-To: <BANLkTi=7Lqyj4LbRPHp1mLo_0-mFN5XPmA@mail.gmail.com>
References: <BANLkTi=ScMyF7nHQ6nhbyYr9UDrq16U=9g@mail.gmail.com>
<BANLkTi=PXV+FrAJHHX56D41Xo4DzG7vADA@mail.gmail.com>
<BANLkTim9sFLt3e2+auF_oTr68UETiW83VA@mail.gmail.com>
<BANLkTi=7Lqyj4LbRPHp1mLo_0-mFN5XPmA@mail.gmail.com>
On Wed, Jun 22, 2011 at 2:39 AM, Samuel Gendler
<sgendler@ideasculptor.com>wrote:
>
> I was able to create the db with --lc_collate=C and get case-sensitive
> sorting that treats spaces 'correctly,' but I have no idea how reliable that
> is with multibyte characters and it almost certainly doesn't handle accented
> characters correctly in languages that have a mix of ascii and non-ascii
> characters, since the non-ascii chars will all sort as greater than the
> ascii chars, which is probably not how things are alphabetized in those
> languages.
>
>
'locale -a' at a command prompt will list all locale's available on a host.
I guess there is no universal utf-8 collation, which isn't that surprising,
given how utf-8 works, though I had hoped that there might be one that at
least attempts to handle all languages that share character codes in some
kind of sensible manner. Perhaps the details of UTF-8 encoding make that
effectively impossible. But it looks as though if you want to handle
accented characters, you would need to set collation to something like
fr_FR.UTF-8, though it looks like all of the UTF-8 collations ignore spaces
and are case-insensitive. I don't know of a way to determine what the
sorting behaviour of an arbitrary collation might be other than
experimentation. You can set LC_ALL to the locale in question and pipe a
test file through the sort command to do a quick test:
LC_ALL=en_US.UTF-8 sort < test.txt
I checked variants like .UTF-8, .iso88591, .iso885915@euro and they
all ignore spaces in all of the languages I checked. It appears that
the only way to get a space-sensitive sort order is to use C
collation. Same goes for capitalization, I think. Which is pretty
ugly, if you ask me.
It looks like it is theoretically possible to modify a locale's
collation behaviour. On my linux system, all locale's with the
space-ignoring, capital-ignoring collation have this entry in the
locale definitions in /usr/share/i18n/locales:
LC_COLLATE
% Copy the template from ISO/IEC 14651
copy "iso14651_t1"
END LC_COLLATE
Looking at the iso14651_t1 file, I see this:
# Any character not precisely specified will be considered as a special
# character and considered only at the last level.
# <U0000>......<U7FFFFFFF> IGNORE;IGNORE;IGNORE;<U0000>......<U7FFFFFFF>
#
# SYMB. N° GLY
#
<U0020> IGNORE;IGNORE;IGNORE;<U0020> # 32 <SP>
<U005F> IGNORE;IGNORE;IGNORE;<U005F> # 33 _
<U0332> IGNORE;IGNORE;IGNORE;<U0332> # 34 <"_>
<U00AF> IGNORE;IGNORE;IGNORE;<U00AF> # 35 - (MACRON)
<U00AD> IGNORE;IGNORE;IGNORE;<U00AD> # 36 <SHY>
<U002D> IGNORE;IGNORE;IGNORE;<U002D> # 37 -
<U002C> IGNORE;IGNORE;IGNORE;<U002C> # 38 ,
<U003B> IGNORE;IGNORE;IGNORE;<U003B> # 39 ;
<U003A> IGNORE;IGNORE;IGNORE;<U003A> # 40 :
There doesn't seem to be any obvious discussion of how to structure the
LC_COLLATE section of a locale definition, but it is clear that telling it
to ignore spaces and punctuation is the problem we are seeing. That seems
like a very strange choice to make as the default for all UTF-8 locales. I
can't find the locale definitions for OS X, which might reveal a more
sensible LC_COLLATE or may just be defaulting back to C collation for
everything.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sgendler@ideasculptor.com, pavel.stehule@gmail.com, eyal@impactsoft.co.il
Subject: Re: a strange order by behavior
In-Reply-To: <BANLkTi=YhCJ_0y83QS3e3buUNXL+7SwU6A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox