Re: a strange order by behavior

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