Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 269A8632E0B for ; Tue, 1 Sep 2009 20:23:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 04879-04 for ; Tue, 1 Sep 2009 23:22:44 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from out02.mta.xmission.com (out02.mta.xmission.com [166.70.13.232]) by mail.postgresql.org (Postfix) with ESMTP id 62707632DD1 for ; Tue, 1 Sep 2009 20:22:51 -0300 (ADT) Received: from in02.mta.xmission.com ([166.70.13.52]) by out02.mta.xmission.com with esmtp (Exim 4.62) (envelope-from ) id 1Micga-0004eK-JZ; Tue, 01 Sep 2009 17:22:48 -0600 Received: from [208.187.158.94] (helo=[172.16.1.225]) by in02.mta.xmission.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1Micga-0000wL-Es; Tue, 01 Sep 2009 17:22:48 -0600 Message-ID: <4A9DACC5.3040504@gmail.com> Date: Tue, 01 Sep 2009 17:22:45 -0600 From: Rob Sargent User-Agent: Thunderbird 2.0.0.23 (X11/20090817) MIME-Version: 1.0 To: Greg Stark CC: Scott Marlowe , "pgsql-sql@postgresql.org" Subject: Re: Odd sort behaviour References: <4A9D8BC4.30906@gmail.com> <4A9DA0DD.7010606@gmail.com> <407d949e0909011604t4613ac5eue8bd8fa65e72abfb@mail.gmail.com> In-Reply-To: <407d949e0909011604t4613ac5eue8bd8fa65e72abfb@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-XM-SPF: eid=; ; ; mid=; ; ; hst=in02.mta.xmission.com; ; ; ip=208.187.158.94; ; ; frm=robjsargent@gmail.com; ; ; spf=neutral X-SA-Exim-Connect-IP: 208.187.158.94 X-SA-Exim-Rcpt-To: gsstark@mit.edu, scott.marlowe@gmail.com, pgsql-sql@postgresql.org, robjsargent@gmail.com X-SA-Exim-Mail-From: robjsargent@gmail.com X-SA-Exim-Version: 4.2.1 (built Thu, 25 Oct 2007 00:26:12 +0000) X-SA-Exim-Scanned: No (on in02.mta.xmission.com); Unknown failure X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.762 tagged_above=-10 required=5 tests=AWL=0.837, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 200909/9 X-Sequence-Number: 33074 OK, I'm waking up now. My locale is as Scott suspected, en-US.UTF-8, and of course my server too. I guess I never really left "C" intellectually :) and we have a server that thinks SQL-ASCII is cool and comparing lists of names and emails between that server and my local utf-8 one was rather perplexing. I'm sure this a life-time's worth of discussion on the merits of treating "." as nothing when sorting.... Sorry for the noise. Greg Stark wrote: > On Tue, Sep 1, 2009 at 11:31 PM, Rob Sargent wrote: > >> How many ways might one accidentally do that I wonder. >> > > Well most operating system distributions ask you when you install them > what region you're in and use a collation for that region. > > In 8.4 you can check what collation a database is set to use with \l > in psql. In 8.3 the entire "cluster" has a single collation which you > can see using "show lc_collate". > > You can see how your system's collations work by running sort: > > $ LC_ALL=c sort s > a.ecke70@gmx.de > a.fischedick@t-online.de > adrianohazim@hotmail.com > adx008@show.org.tw > aecheniq@mac.com > aelefant@unina.it > aeo_tw@hotmail.com > aflores3432@gmail.com > afried@advancedneurosurgeons.com > agave007@comcast.net > agelsinger@amirsys.com > agis1doc@yahoo.gr > > $ LC_ALL=en_US sort s > adrianohazim@hotmail.com > adx008@show.org.tw > aecheniq@mac.com > a.ecke70@gmx.de > aelefant@unina.it > aeo_tw@hotmail.com > a.fischedick@t-online.de > aflores3432@gmail.com > afried@advancedneurosurgeons.com > agave007@comcast.net > agelsinger@amirsys.com > agis1doc@yahoo.gr > >