Thread

1. '_' < '5' -- different answer on 7.2 and 7.3

   David Blasby <dblasby@refractions.net> — 2003-07-04T21:01:12Z

   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
   
   
   

2. Re: '_' < '5' -- different answer on 7.2 and 7.3

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-07-04T21:18:05Z

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

3. Re: '_' < '5' -- different answer on 7.2 and 7.3

   David Blasby <dblasby@refractions.net> — 2003-07-04T23:00:40Z

   > 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
   
   
   
   

4. Re: '_' < '5' -- different answer on 7.2 and 7.3

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-07-04T23:34:31Z

   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.