Обсуждение: 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