Re: ORDER BY Problem

Severin Olloz <S.Olloz@soid.ch> writes:

> Why does Postgresql order the uppercase letters first?

That is locale dependent - if you don't use a proper locale but use
straight ASCII, A...Za...d would be correct. If you set a locale,
you'll get (Aa)(Bb) etc instead, which is the correct way to sort in
human languages.

> I have e.g. a table with one row an in this row there are follow values:
>
> row1
> ----
> ADC
> aa
> ABC
>
> With this select-syntax
>
> select * from table order by row1
>
> I become this output
>
> ABC
> ADC
> aa

Note that "aa" can give you some surprises anyway... e.g. in
Norwegian, "aa" is mapped to "å", the last character in the alphabet
(in Norwegian words, not foreign names etc... this distinction is
obviously rather hard to sort by for computers:).

Note that not using locales also makes "ORDER BY" give other bogus
results - as ASCII (or latin1) doesn't know the local alphabets, it
can't sort according to them. Example, for Norwegian:


create table bar(
    ord varchar(40),
     foo int,
    primary key(ord));

insert into bar values('ære',2);
insert into bar values('åre',3);
insert into bar values('are',4);
insert into bar values('zsh',5);
insert into bar values('begynne',6);
insert into bar values('øve',7);

select ord,foo from bar order by ord;

Here is a valid result:

 are     |   4
 begynne |   6
 zsh     |   5
 ære     |   2
 øve     |   7
 åre     |   3

Here is an invalid result:

 are     |   4
 begynne |   6
 zsh     |   5
 åre     |   3
 ære     |   2
 øve     |   7

The invalid result is what you'll get if you run postgresql without
locale information, so make sure you set one before you initialize the
database and run the postmaster.
--
Trond Eivind Glomsrød
Red Hat, Inc.