Обсуждение: String operators <= and >=
Hi We have a strange problem when searching for a string using <= and >= operators on Solaris. It did work with 7.3, but not with 7.3.3, 7.3.4 and 7.4.1. The database have been initialized with locale en_US.ISO8859-1 Have a simple table testtbl: create table testtbl ( id int, name varchar(20)); Insert this record: insert into testtbl values(10, 'PEREZ'); Now do that search: select * from testtbl where name >= 'PERE' and name <= 'PERE\377'; It should return the record of the table, but it will not return it with version 7.3.3 and later. We still found that workaround works: select * from testtbl where name >= 'PERE' and name <= 'PEREzz'; Which is pretty strange... Any comments about that? Is it a bug? Thanks, Patrick _________________________________________________________________ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=dept/features&pgmarket=en-ca&RU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca
"Patrick Vachon" <kaleidelf@hotmail.com> writes: > We have a strange problem when searching for a string using <= and >= > operators on Solaris. > It did work with 7.3, but not with 7.3.3, 7.3.4 and 7.4.1. > The database have been initialized with locale en_US.ISO8859-1 > Have a simple table testtbl: > create table testtbl ( id int, name varchar(20)); > Insert this record: > insert into testtbl values(10, 'PEREZ'); > Now do that search: > select * from testtbl where name >= 'PERE' and name <= 'PERE\377'; I would expect this not to find PEREZ in that locale, no matter which PG version you use. Try putting the three strings in a file and sorting them with the sort(1) command-line tool --- in en_US locale you'll likely find that 'PERE\377' comes between the others. For instance, on HPUX I get: $ cat zzz PEREZ PERE PERE� $ LC_ALL=C sort zzz PERE PEREZ PERE� $ LC_ALL=en_US.iso88591 sort zzz PERE PERE� PEREZ $ My guess is that your 7.3 installation used C locale, and you failed to reproduce that setting when you installed the newer versions. regards, tom lane
It is quite possible! I will have to test that to confirm. Is there any way to change the locale without having to re-initdb? Thanks Patrick >From: Tom Lane <tgl@sss.pgh.pa.us> >To: "Patrick Vachon" <kaleidelf@hotmail.com> >CC: pgsql-ports@postgresql.org >Subject: Re: [PORTS] String operators <= and >= Date: Thu, 26 Feb 2004 >14:33:19 -0500 > >"Patrick Vachon" writes: > > We have a strange problem when searching for a string using <= and >= > > operators on Solaris. > > It did work with 7.3, but not with 7.3.3, 7.3.4 and 7.4.1. > > The database have been initialized with locale en_US.ISO8859-1 > > > Have a simple table testtbl: > > create table testtbl ( id int, name varchar(20)); > > Insert this record: > > insert into testtbl values(10, 'PEREZ'); > > Now do that search: > > select * from testtbl where name >= 'PERE' and name <= 'PERE\377'; > >I would expect this not to find PEREZ in that locale, no matter which PG >version you use. Try putting the three strings in a file and sorting >them with the sort(1) command-line tool --- in en_US locale you'll >likely find that 'PERE\377' comes between the others. For instance, >on HPUX I get: > >$ cat zzz >PEREZ >PERE >PERE� >$ LC_ALL=C sort zzz >PERE >PEREZ >PERE� >$ LC_ALL=en_US.iso88591 sort zzz >PERE >PERE� >PEREZ >$ > >My guess is that your 7.3 installation used C locale, and you failed to >reproduce that setting when you installed the newer versions. > > regards, tom lane _________________________________________________________________ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=dept/bcomm&pgmarket=en-ca&RU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca
"Patrick Vachon" <kaleidelf@hotmail.com> writes: > Is there any way to change the locale without having to re-initdb? Nope :-(. regards, tom lane