On Wed, May 16, 2012 at 3:46 PM, Wes James <compte...@gmail.com> wrote:
> > > On Mon, May 14, 2012 at 5:00 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Wes James <compte...@gmail.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.
