From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Samuel Gendler <sgendler(at)ideasculptor(dot)com> |
Cc: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il>, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: a strange order by behavior |
Date: | 2011-06-22 14:49:09 |
Message-ID: | 29907.1308754149@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Samuel Gendler <sgendler(at)ideasculptor(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2011-06-22 20:10:15 | Re: a strange order by behavior |
Previous Message | Pavel Stehule | 2011-06-22 13:44:00 | Re: a strange order by behavior |