On Thu, 5 Jun 2003, Dave Wood wrote:
> 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.
It's not a bug. We sort using the rules for the locale you chose at initdb
time. The definition of en_US's sort rules on your system ignore case,
spaces and some if not all symbols (I also use RedHat 9). To watch this,
make sure you're in en_US and use the sort utility on arbitrary data.
If you want more typical computer sort rules, you'll want to initdb with
the "C" locale (I generally do something like LANG="C" initdb -D ... )