Actually Greg . . .
After a long and careful study of this, this is a Postgres bug.
Postgres is boogering up the sort. The operators < >, and the "order by"
in the select statement are both case-sensitive and case-insensitive. It
is inconsistent. It does not matter what my collating sequence is. This
is the behavior I am seeing:
If the words are the same words but letters have different case, then the
operator is case-sensitive.
If the words are not the same words, then the operator is case-insensitive
until the operator reaches the character position in both strings where the
letters become different.
It is a combination of both case-sensitive and case-insenstive. Completely
bogus. It does not matter what computer I am using. I get the same
bogus behavior on all my operating systems and Postgres versions.
4605 Woodmill Run
Apex, NC 27539
From: gsstark(at)gmail(dot)com [mailto:gsstark(at)gmail(dot)com] On Behalf Of Greg Stark
Sent: Thursday, August 20, 2009 5:50 PM
To: Brian Ceccarelli
Subject: Re: BUG #4999: select 'a' < 'A' is true, but should be false . . .
On Thu, Aug 20, 2009 at 9:24 PM, Brian
> The operator equals is case sensitive.
> The operator < and > are case-insensitive.
> This is not consistent.
> Most of the time, operator < and > are case-insenstive, until you compare
> the upper and lower cases of the same letter.
This is all down to your collation locale. en_US and most other non-C
locales sort case insensitively. You'll find the Unix command "sort"
and other utilities behave similarly. You can set your lc_collate to C
if you want strict ascii binary byte order. In 8.3 and previous this
was a cluster-wide setting but in 8.4 you can set it per-database.
If you can find any cases that are actually inconsistent (the above
examples are not -- you need to find three values where a < b < c but
a >= c) then this is a serious problem and you should not use that
locale with postgres or it will result in corrupt indexes.
In response to
pgsql-bugs by date
|Next:||From: Tom Lane||Date: 2009-08-26 02:18:16|
|Subject: Re: BUG #4999: select 'a' < 'A' is true, but should be false . . . |
|Previous:||From: WANGRUNGVICHAISRI, SHIVESH||Date: 2009-08-26 01:25:50|
|Subject: Re: BUG #4996: postgres.exe memory consumption keeps going up|