Thread: Different sort order

I'm wondering why the sort order on these two servers behaves differently.
The only difference I can see is that one is using a 32.bits Ubuntu and
the other is on 64bits versions of Ubuntu.
But why this difference ?

Thanks,
 Poul


mydb=# select * from test order by felt1;
 felt1
--------
 test1
 test_1
 test2
 test_2
 test3
 test_3
(6 rows)

mydb=# \encoding
UTF8
mydb=# select version();

version
------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.9 on i486-pc-linux-gnu, compiled by GCC gcc-4.0.gcc-opt
(GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5)


mydb=# select * from test order by felt1;
 felt1
--------
 test1
 test2
 test3
 test_1
 test_2
 test_3
(6 rows)

mydb=# \encoding
UTF8
mydb=# select version();

version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.9 on x86_64-pc-linux-gnu, compiled by GCC
gcc-4.0.gcc-opt (GCC) 4.0.3 (Ubuntu 4.0.3-1ubuntu5)
(1 row)

On Mon, May 28, 2007 at 07:07:41PM +0200, Poul Møller Hansen wrote:
> I'm wondering why the sort order on these two servers behaves differently.

What's the output of the following query on each server?

select name, setting from pg_settings where name ~ '^lc_';

--
Michael Fuhr

Poul Møller Hansen <freebsd@pbnet.dk> writes:

> I'm wondering why the sort order on these two servers behaves differently.
> The only difference I can see is that one is using a 32.bits Ubuntu and the
> other is on 64bits versions of Ubuntu.
> But why this difference ?

Try on both servers:

  show lc_collate

Unfortunately you have to initdb to set the locale. You can't set it per
database or change it once it's initialized.


This one looks like en_US or something similar:

> mydb=# select * from test order by felt1;
> felt1 --------
> test1
> test_1
> test2
> test_2
> test3
> test_3
> (6 rows)
>

And this one looks like C:

> mydb=# select * from test order by felt1;
> felt1 --------
> test1
> test2
> test3
> test_1
> test_2
> test_3
> (6 rows)


--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

> What's the output of the following query on each server?
>
> select name, setting from pg_settings where name ~ '^lc_';
>
Ahh there's a difference
mydb=# select name, setting from pg_settings where name ~ '^lc_';
    name     | setting
-------------+---------
 lc_collate  | en_US
 lc_ctype    | en_US
 lc_messages | en_US
 lc_monetary | en_US
 lc_numeric  | en_US
 lc_time     | en_US

mydb=# select name, setting from pg_settings where name ~ '^lc_';
    name     | setting
-------------+---------
 lc_collate  | C
 lc_ctype    | C
 lc_messages | C
 lc_monetary | C
 lc_numeric  | C
 lc_time     | C

I guess the reason is that PostgreSQL got installed before setting the
locale,
and the only way to change it is using initdb --locale.

Poul

> Try on both servers:
>
>   show lc_collate
>
> Unfortunately you have to initdb to set the locale. You can't set it per
> database or change it once it's initialized.
>
>
>
Right you are, but I suppose I can do a dump using pg_dumpall overwrite the cluster
with initdb --locale=en_US and restore it again without the different collation giving problems ?

Poul