Different sort order

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