help understanding collation order

From: raf <raf(at)raf(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: help understanding collation order
Date: 2011-01-17 02:22:41
Message-ID: 20110117022240.GA16484@raf.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi,

postgresl-8.4.6 (database encoding is utf8)

the following sql behaves strangely on one of my servers:

create table ttt(id serial not null primary key, name text);
insert into ttt (name) values ('CLARKE, DAVID');
insert into ttt (name) values ('CLARK, PETER');
insert into ttt (name) values ('CLARKE');
insert into ttt (name) values ('CLARK');
select * from ttt order by name;
drop table ttt;

the behaviour i expect (and see on macosx-10.6.6) is:

id | name
----+---------------
4 | CLARK
2 | CLARK, PETER
3 | CLARKE
1 | CLARKE, DAVID

the behaviour i don't expect but see anyway (on debian-5.0) is:

id | name
----+---------------
4 | CLARK
3 | CLARKE
1 | CLARKE, DAVID
2 | CLARK, PETER

the "good" server has lc_messages='en_AU' and the
"bad" server has lc_messages="en_AU.utf8" which may
be relevant but i can't successfully alter these
values (i.e. postgres doesn't restart after i try)
so i'm hoping it's not relevant.

can anyone explain what i've done wrong here and
suggest what i can do to fix it?

cheers,
raf

p.s. the postgres installation was PostgresPlus/8.4SS that
was subsequently upgraded via postgres one click installers.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ivan Voras 2011-01-17 02:26:33 Re: Optimal settings for embedded system running PostgreSQL
Previous Message Andrej 2011-01-17 00:40:24 Re: iPad and Postgresql...