Re: order by different on mac vs linux

On Mon, May 14, 2012 at 4:42 PM, Wes James <comptekki(at)gmail(dot)com> wrote:

> I have postgresql 9.1.3 on a mac and on linux.
>
> On the mac the results come out:
>
> ! *`-=[];',./~(at)#$%^&()_+{}|:"<>?\
>
> then
>
> \--\
>
> On ubuntu 12.04 x64 it comes out (compiled and installed postgres from
> tbz2 from postgresql.org repo):
>
> \--\
>
> then
>
> ! *`-=[];',./~(at)#$%^&()_+{}|:"<>?\
>
>
> Why is there a different order on the different platforms. I even
> copied the data folder on the mac to the linux box instead of doing
> pg_dump and then \i pg.sql file on Linux to move the data and the
> order is still different like this.
>
> Any idea why? the ! line should come before the \ line, lexically. !
> is char 33 and \ is char 92.
>
> This is done with
>
> select * from table order by field;
>
> on both systems and "field" is character varying on both systems.
>
>
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? 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.

Here is a dump of a db that you can test on ubuntu.

https://gist.github.com/2802071

do:

select * from dbooks order by title;

in psql

and it won't sort correctly. Add a record with something like "!a" in the
title and run the query above and it won't sort properly. (This is
unverified as I don't have linux in front of me right now - but I did add
records and they did not sort properly with "order by title")

-wes