Обсуждение: '_' < '5' -- different answer on 7.2 and 7.3

I noticed a change between our 7.2 and 7.3 postgresql database.

On 7.2:

template1=>  select '_' < '5'; ?column?
---------- f
(1 row)



On 7.3:

template1=#  select '_' < '5'; ?column?
---------- t
(1 row)


Any reason for this change?

dave

On Fri, 4 Jul 2003, David Blasby wrote:

> I noticed a change between our 7.2 and 7.3 postgresql database.
>
> On 7.2:
>
> template1=>  select '_' < '5';
>   ?column?
> ----------
>   f
> (1 row)
>
>
>
> On 7.3:
>
> template1=#  select '_' < '5';
>   ?column?
> ----------
>   t
> (1 row)
>
>
> Any reason for this change?

My first guess would be that you're not running in "C" locale
on the 7.3 system.  I get false on my 7.3.1 system in C locale,
but if I compare the two strings in C using en_US for example I
seem to get results like the above ('_'<'5' is true).

> My first guess would be that you're not running in "C" locale
> on the 7.3 system.  I get false on my 7.3.1 system in C locale,
> but if I compare the two strings in C using en_US for example I
> seem to get results like the above ('_'<'5' is true).

It turns out our 7.3 database was somehow initd with local "en_US".

I'm trying to get postgresql and a MS vc++ to communicate.

In postgresql 7.3 (en_US):

toponymy=# select '_' < '5';; ?column?
---------- t
(1 row)


toponymy=# select '_5' < '5'; ?column?
---------- f
(1 row)


But in MS vc++:
TRACE("locale set to 'en_US'\n");setlocale( LC_ALL, "English_United States" );if (strcoll("_5","5") <0 )
TRACE("strcoll('_5','5')--  <0 \n");else    TRACE( "strcoll('_5','5') -- >=0\n");
 

returns:
locale set to 'en_US'
strcoll('_5','5') --  <0


Which is to say postgresql thinks "_5" > "5", but
(a bit strangely) "_" < "5" (the '>' and '<' are reversed).

vc++ thinks "_5" < "5" and "_" < "5".

So, which one is correct and why does the other disagree?

dave

On Fri, 4 Jul 2003, David Blasby wrote:

> > My first guess would be that you're not running in "C" locale
> > on the 7.3 system.  I get false on my 7.3.1 system in C locale,
> > but if I compare the two strings in C using en_US for example I
> > seem to get results like the above ('_'<'5' is true).
>
> It turns out our 7.3 database was somehow initd with local "en_US".

> But in MS vc++:
>
>     TRACE("locale set to 'en_US'\n");
>     setlocale( LC_ALL, "English_United States" );
>     if (strcoll("_5","5") <0 )
>         TRACE("strcoll('_5','5') --  <0 \n");
>     else
>         TRACE( "strcoll('_5','5') -- >=0\n");
>
> returns:
> locale set to 'en_US'
> strcoll('_5','5') --  <0
>
>
> Which is to say postgresql thinks "_5" > "5", but
> (a bit strangely) "_" < "5" (the '>' and '<' are reversed).
>
> vc++ thinks "_5" < "5" and "_" < "5".
>

> So, which one is correct and why does the other disagree?

Probably different definitions of the locale.

What type of system is the server on?
Under Redhat 9, en_US, doing a small C program

#include <stdio.h>
#include <locale.h>
#include <string.h>

int main() {       setlocale( LC_ALL, "en_US" );       printf("%d\n", strcoll("_5", "5"));       printf("%d\n",
strcoll("_","5"));
 
}

I get
1
-1

Which would appear to match what you're seeing from PostgreSQL.