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


Fri, 04 Jul 2003 14:01:12 -0700
David Blasby <dblasby@refractions.net>
'_' <  '5'  -- different answer on 7.2 and 7.3

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).




Fri, 4 Jul 2003 14:18:05 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: '_' <  '5'  -- different answer on 7.2 and 7.3
> 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



Fri, 04 Jul 2003 16:00:40 -0700
David Blasby <dblasby@refractions.net>
Re: '_' <  '5'  -- different answer on 7.2 and 7.3

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.



Fri, 4 Jul 2003 16:34:31 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: '_' <  '5'  -- different answer on 7.2 and 7.3