From: Peter Eisentraut <peter_e@gmx.net>
To: Samuel Gendler <sgendler@ideasculptor.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 23:10:15 +0300
Message-ID: <1308773415.10498.6.camel@vanquo.pezone.net> (raw)
In-Reply-To: <BANLkTi=PXV+FrAJHHX56D41Xo4DzG7vADA@mail.gmail.com>
References: <BANLkTi=ScMyF7nHQ6nhbyYr9UDrq16U=9g@mail.gmail.com>
<BANLkTi=PXV+FrAJHHX56D41Xo4DzG7vADA@mail.gmail.com>
On ons, 2011-06-22 at 01:43 -0700, Samuel Gendler wrote:
> I seem to recall a thread here about it ignoring spaces entirely in that
> collation (and maybe ignoring capitalization, too?).
The way it works is that every collating element (letter or other
character or character group that you sort as a unit) is assigned four
weights (primary, secondary, tertiary, and quaternary), and the sorting
then first compares the primary weights, then the secondary weights,
etc. The primary weight typically indicates the overall sort order,
like A before B, the secondary weight has to do with diacritic marks,
the tertiary with letter case, and the fourth level is only used in
special cases. So that's why it looks as though the capitalization is
"ignored" unless both the primary and secondary weights are the same.
> This worked:
>
> createdb -E UTF-8 --lc-collate=C some_db
>
> A quick google search
> reveals that there is some kind of standard for unicode collation (
> http://www.unicode.org/reports/tr10/ ) and I have no idea if that is what is
> represented by the en_US.UTF-8 collation or not.
At least the collate category of the en_US.UTF-8 locale on glibc is
unaltered from the ISO 14651 default ordering, which is equivalent to
the Unicode default ordering. There several other locales for which
that is also the case. Unfortunately, this is not exposed outside of
the glibc source code. So you can't just select "give me a neutral
default ordering".
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: peter_e@gmx.net, sgendler@ideasculptor.com, eyal@impactsoft.co.il
Subject: Re: a strange order by behavior
In-Reply-To: <1308773415.10498.6.camel@vanquo.pezone.net>
* 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