Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 78653B5DBE4 for ; Wed, 22 Jun 2011 11:49:18 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 91508-02 for ; Wed, 22 Jun 2011 14:49:12 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 45F90B5DBE8 for ; Wed, 22 Jun 2011 11:49:12 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p5MEn9v8029908; Wed, 22 Jun 2011 10:49:09 -0400 (EDT) To: Samuel Gendler cc: Pavel Stehule , Eyal Wilde , pgsql-sql@postgresql.org Subject: Re: a strange order by behavior In-reply-to: References: Comments: In-reply-to Samuel Gendler message dated "Wed, 22 Jun 2011 02:39:34 -0700" Date: Wed, 22 Jun 2011 10:49:09 -0400 Message-ID: <29907.1308754149@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201106/83 X-Sequence-Number: 35549 Samuel Gendler 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