From: Tom Lane <tgl@sss.pgh.pa.us>
To: Samuel Gendler <sgendler@ideasculptor.com>
Cc: 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 10:49:09 -0400
Message-ID: <29907.1308754149@sss.pgh.pa.us> (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>
Samuel Gendler <sgendler@ideasculptor.com> writes:
> Interesting. The original thread to which I was referring has a subject of
> "Sorting Issue" and the original request showed a list of vehicle model
> names which were sorting as though there were no spaces. The user had
> collation set to en_US.UTF-8. However, my database (on OS X) sorts both his
> example and the example that started this thread correctly, despite my
> lc_collate being set to the same value.
OS X's support for comparisons in UTF-8 locales is widely understood
to be broken. In general, as you've found out, there's little
compatibility in sort order across platforms; but OS X is just
completely unlike other modern Unixoid platforms. I would not take it
as the definition of "correct".
AFAIK, if you're looking for strict character-by-character sorting
rather than those weird "dictionary" rules, C locale (a/k/a POSIX
locale) is your only choice, and you have to put up with the odd sort
order you will get for all non-ASCII characters. In principle a locale
could be defined that does character-by-character but does something
reasonable with non-ASCII; but none of the major platforms seem to offer
one.
Also, just to be perfectly clear: this is not Postgres' fault, it's
just sorting the way strcoll() says to. You'll get the same sort
order from the command-line sort(1) program, if you feed it the same
data in the same locale environment.
regards, tom lane
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: tgl@sss.pgh.pa.us, sgendler@ideasculptor.com, pavel.stehule@gmail.com, eyal@impactsoft.co.il
Subject: Re: a strange order by behavior
In-Reply-To: <29907.1308754149@sss.pgh.pa.us>
* 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