[SQL] order by different on mac vs linux

#1

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.

Thanks,

Wes

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#2

Wes James <comptekki (AT) gmail (DOT) com> writes:

Quote:

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 ...)

If you want consistent cross-platform results, "C" locale will get
that for you, but it's pretty stupid about non-ASCII characters.

For more info read
http://www.postgresql.org/docs/9.1/static/charset.html

regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#3

On Mon, May 14, 2012 at 5:00 PM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

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 ...)

If you want consistent cross-platform results, "C" locale will get
that for you, but it's pretty stupid about non-ASCII characters.

For more info read
http://www.postgresql.org/docs/9.1/static/charset.html

* * * * * * * * * * * *regards, tom lane

Thanks,

I'll take a look.

-wes

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#4

On Mon, May 14, 2012 at 5:00 PM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

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 ...)

If you want consistent cross-platform results, "C" locale will get
that for you, but it's pretty stupid about non-ASCII characters.

For more info read
http://www.postgresql.org/docs/9.1/static/charset.html

* * * * * * * * * * * *regards, tom lane

I checked both and they both say this:

show lc_collate;
lc_collate
-------------
en_US.UTF-8
(1 row)

You mention OS X is broken, but it looks like the linux box is broken
in this case.

With the current database I have, it looks like everything is sorting
correctly on both platforms, except those two records (the two lines
from my first email) which I added later for testing characters and
escaping, etc. on a web app.

-wes

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#5

On Mon, May 14, 2012 at 5:00 PM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

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 ...)

If you want consistent cross-platform results, "C" locale will get
that for you, but it's pretty stupid about non-ASCII characters.

For more info read
http://www.postgresql.org/docs/9.1/static/charset.html

* * * * * * * * * * * *regards, tom lane

I tried using the postgres that comes with ubuntu (sudo apt-get
install postgresql).

With my app I kept getting invalid password. I went in to the
database sudo -u postgres database and did \password and set a
password, but I still got invalid password error from the app api
trying to make a connection.

I then went back to the source installed version and now the output is
correct. I'm not sure what changed. hmmm.

-wes

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#6

On Tue, May 15, 2012 at 10:06 AM, Wes James <comptekki (AT) gmail (DOT) com> wrote:

Quote:

On Mon, May 14, 2012 at 5:00 PM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.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 ...)

If you want consistent cross-platform results, "C" locale will get
that for you, but it's pretty stupid about non-ASCII characters.

For more info read
http://www.postgresql.org/docs/9.1/static/charset.html

* * * * * * * * * * * *regards, tom lane

I tried using the postgres that comes with ubuntu (sudo apt-get
install postgresql).

With my app I kept getting invalid password. *I went in to the
database sudo -u postgres database and did \password and set a
password, but I still got invalid password error from the app api
trying to make a connection.

I then went back to the source installed version and now the output is
correct. *I'm not sure what changed. hmmm.

It's most likely an issue with the settings in your pg_hba.conf file.

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#7

On Tue, May 15, 2012 at 10:16 AM, Scott Marlowe <scott.marlowe (AT) gmail (DOT) com> wrote:

Quote:

On Tue, May 15, 2012 at 10:06 AM, Wes James <comptekki (AT) gmail (DOT) com> wrote:
On Mon, May 14, 2012 at 5:00 PM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.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 ...)

If you want consistent cross-platform results, "C" locale will get
that for you, but it's pretty stupid about non-ASCII characters.

For more info read
http://www.postgresql.org/docs/9.1/static/charset.html

* * * * * * * * * * * *regards, tom lane

I tried using the postgres that comes with ubuntu (sudo apt-get
install postgresql).

With my app I kept getting invalid password. *I went in to the
database sudo -u postgres database and did \password and set a
password, but I still got invalid password error from the app api
trying to make a connection.

I then went back to the source installed version and now the output is
correct. *I'm not sure what changed. hmmm.

It's most likely an issue with the settings in your pg_hba.conf file.

Ok - if I try the apt-get version, I'll look at that. The apt-get
version is a cluster version. Looks like that file is in
/etc/postgresql/9.1/dbname

I see

local all postgres peer
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5

in there.

-wes

--
Sent via pgsql-sql mailing list (pgsql-sql (AT) postgresql (DOT) org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

#8

On Mon, May 14, 2012 at 5:00 PM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

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?

-wes

#9

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

Quote:

On Mon, May 14, 2012 at 5:00 PM, Tom Lane <tgl (AT) sss (DOT) pgh.pa.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.

#10

On Wed, May 16, 2012 at 5:00 PM, Samuel Gendler
<sgendler (AT) ideasculptor (DOT) com>wrote:

Quote:

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.pa.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.

Why are people saying os x is broken in my case? Looking at

http://www.utf8-chartable.de/ and http://www.asciitable.com/ for sort
order, ! should come before apache. On os x it is correct, on ubuntu linux
it is not. In the order by output per my previous emails, it is correct on
os x, but no on linux. Why do people keep saying os x is broken, when it
is doing the correct thing from what I've seen?

-wes