On Mon, May 14, 2012 at 4:42 PM, Wes James
<comptekki@gmail.com> wrote:
I have postgresql 9.1.3 on a mac and on linux.
On the mac the results come out:
! *`-=[];',./~@#$%^&()_+{}|:"<>?\
then
\--\
On ubuntu 12.04 x64 it comes out (compiled and installed postgres from
tbz2 from postgresql.org repo):
\--\
then
! *`-=[];',./~@#$%^&()_+{}|:"<>?\
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.
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