To: pgsql-sql@postgresql.org
From: Jasen Betts <jasen@xnet.co.nz>
Subject: Re: order by different on mac vs linux
Date: 27 May 2012 05:41:33 GMT
Organization: Dis (not Dat) Organisation
Lines: 54
Message-ID: <jpseqd$rg0$1@reversiblemaps.ath.cx>
References: 
 <CAFjCMHse4AHdTav96ifSsKaqq1Ggj43Xc=DPBfwCzZyuYKAJFQ@mail.gmail.com>
	<CAFjCMHsjrP-mP8YJay_izDooCAjFmnjopq1M9Pbv+-HH3c_UUg@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-18 (Linux)

On 2012-05-27, Wes James <comptekki@gmail.com> wrote:
>>
>> Why is there a different order on the different platforms. 

postgres uses the text comparison operators provided by the host
platform.  for linux these are defined in the locales package
this may be part of the glibc package.

> I'd like to bring up my question again after testing on windows xp.  Why
> does xp and mac os x sort properly when linux does not? 

osx and windows hare broken sorting,

> I tested this last
> week with ubuntu 11.10 and it is doing the same thing as 12.04.  UTF8
> encoding and lc_collate =  en_US.UTF-8 on mac and linux and american_usa on
> windows which is the same thing as en_US.UTF-8.  All are using UTF8
> encoding.
>
> I'd really like to use linux to host this DB (and have it hosted in vmware
> ESXi), but the order is not coming out correct.  I've got foreign titles of
> books in this db, so I need the UTF8 all the way through.  "C" won't cut it.

I briefly worked for a library software company.  They had defined their own
ordering operator in the form of a function that mangled strings (they were 
using paradox database)

As I understand it postgres allows you to define your own types with
their own ordering operators 

even ignoring the issue of non-alphabetic symbols there are problems
that should be addressed for sorting titles and names. Many of these
are due to abbreviations which shouls be sorted asif expanded.

some examples:

 St. Stephen of Hungary
 Saint Stephen the Martyr
 St. Rita
 Street art.
 
 Wall Stickers
 Wall st.
 Wall Street
 
 McArthur
 MacArthur, Douglass
 Maccaroni

 Wye Road
 Y Road

-- 
⚂⚃ 100% natural