Re: order by different on mac vs linux

On Wed, May 16, 2012 at 3:46 PM, Wes James <comptekki(at)gmail(dot)com> wrote:

>
>
> On Mon, May 14, 2012 at 5:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Wes James <comptekki(at)gmail(dot)com> writes:
>> > Why is there a different order on the different platforms.
>>
>> This is not exactly unusual. You should first check to see if
>> lc_collate is set differently in the two installations --- but even if
>> it's the same, there are often platform-specific interpretations of
>> the sorting rules. (Not to mention that OS X is flat out broken when
>> it comes to sorting UTF8 data ...)
>>
>>
> I just ran these:
>
> linux:
>
> on linux
>
> # SELECT CASE WHEN 'apache' > '!yada' THEN 'TRUE' ELSE 'FALSE' END FROM
> pg_user;
> case
> -------
> FALSE
> (1 row)
>
> # show lc_collate;
>
> lc_collate
> -------------
> en_US.UTF-8
> (1 row)
>
> ------------------------
>
> on mac os x:
>
> # SELECT CASE WHEN 'apache' > '!yada' THEN 'TRUE' ELSE 'FALSE' END FROM
> pg_user;
> case
> ------
> TRUE
> (1 row)
>
> # show lc_collate;
> lc_collate
> -------------
> en_US.UTF-8
> (1 row)
>
>
> -----------------------
>
> Why is the linux postgres saying false with the lc_collage set the way it
> is?
>

That's the point - UTF-8 collation is just completely broken under OS X.
There's much previous discussion of the topic on this list and elsewhere.
If you're developing on OS X but running linux and you are mostly using an
ascii character set in your test dataset, set your development OS X boxes
to use C collation, which will basically do what you expect it do do until
you start throwing multibyte characters at it. If you can't constrain your
testing/development dataset in such a manner and collation order really
matters during development, then you probably shouldn't develop on OS X. I
spent a fair amount of time investigating how to define a new charset in
what proved to ultimately be a futile attempt to redefine UTF-8 on OSX to
behave just like ti does on Linux. I just gave it up after wasting a few
too many hours on it. It may be possible to do it, but the return on
invested time was non-existent for me so I abandoned my effort.