I'm having a problem where the ORDER BY clause is not performing as I
would expect. I created a small set of test data:
create table orderbytest (name varchar(20));
insert into orderbytest values ('a');
insert into orderbytest values ('- b');
insert into orderbytest values ('c');
insert into orderbytest values ('- d');
insert into orderbytest values ('e');
insert into orderbytest values ('- F');
insert into orderbytest values ('-- g');
insert into orderbytest values ('-- h');
insert into orderbytest values ('I');
insert into orderbytest values ('j');
select * from orderbytest order by name;
Running this in oracle, I get the following results, which are the
results that I would expect:
NAME
-----
- F
- b
- d
-- g
-- h
I
a
c
e
j
Running the same in Postgresql 7.3 I get
name
------
a
- b
c
- d
e
- F
-- g
-- h
I
j
Postgres not only seems to ignore the case of the text, implying a
clause of "order by upper(name)", but it also seems to drop characters
when doing the sort (the "-" and " " preceding the letters are not used
during the order by). Doing some digging on this subject seemed to
imply that my locale was not set correctly somehow. This is an
out-of-the-box install of postgres from RedHat 9 in the en_US locale. I
have confirmed this behavior on another out-of-the-box install on RedHat
8.
If this isn't considered a bug, then could somebody please enlighten me
as to the proper use of ORDER BY? In other words, how do I get postgres
to order by using a sort typical of every text sort I've ever seen?
Once that takes case into consideration and doesn't pick and choose
which characters it will use for the sort.
Thanks,
-- Dave Wood