Postgres Pro
Downloads
Home   >   mailing lists

Thread: ~*, case insensitiveness and national chars

~*, case insensitiveness and national chars

From
Marcin Owsiany
Date:
Hi,

Consider the following commands:

] create database a encoding='LATIN2';
] \connect a
] create table a (a character(1));
] insert into a values(chr(161));
] insert into a values(chr(177));
] select count(a) from a where a ~* chr(161);

On PostgreSQL 7.3.2 this returns 2 (which is correct, since chr(161) and
chr(177) are the same character in LATIN2, just different case), but on
postgresql 7.2.2, this returns 1.

This suggests that the behavior of 7.2.2 is a bug, and something was
fixed between those two versions. Could someone point me at relevant
source file so that I can correct this in 7.2.2 (since unfortunately I
can't just upgrade to 7.3.2 now).

regards,

Marcin
--
Marcin Owsiany <marcin@owsiany.pl>              http://marcin.owsiany.pl/
GnuPG: 1024D/60F41216  FE67 DA2D 0ACA FC5E 3F75  D6F6 3A0D 8AA0 60F4 1216

"Every program in development at MIT expands until it can read mail."
                                                              -- Unknown

Re: ~*, case insensitiveness and national chars

From
Tom Lane
Date:
Marcin Owsiany <marcin@owsiany.pl> writes:
> Consider the following commands:

> ] create database a encoding='LATIN2';
> ] \connect a
> ] create table a (a character(1));
> ] insert into a values(chr(161));
> ] insert into a values(chr(177));
> ] select count(a) from a where a ~* chr(161);

> On PostgreSQL 7.3.2 this returns 2 (which is correct, since chr(161) and
> chr(177) are the same character in LATIN2, just different case), but on
> postgresql 7.2.2, this returns 1.

Case equivalences are determined by locale, not encoding.  Did you build
7.2 with locale support at all?  Did you initdb it in an appropriate
locale?

            regards, tom lane

Re: ~*, case insensitiveness and national chars

From
Marcin Owsiany
Date:
On Thu, Mar 13, 2003 at 07:25:45PM -0500, Tom Lane wrote:
> Marcin Owsiany <marcin@owsiany.pl> writes:
> > Consider the following commands:
>
> > ] create database a encoding='LATIN2';
> > ] \connect a
> > ] create table a (a character(1));
> > ] insert into a values(chr(161));
> > ] insert into a values(chr(177));
> > ] select count(a) from a where a ~* chr(161);
>
> > On PostgreSQL 7.3.2 this returns 2 (which is correct, since chr(161) and
> > chr(177) are the same character in LATIN2, just different case), but on
> > postgresql 7.2.2, this returns 1.
>
> Case equivalences are determined by locale, not encoding.  Did you build
> 7.2 with locale support at all?

Yes.

> Did you initdb it in an appropriate locale?

It was a long time ago, but pg_controldata says I didn't.

Thanks! :-)

Marcin
--
Marcin Owsiany <marcin@owsiany.pl>              http://marcin.owsiany.pl/
GnuPG: 1024D/60F41216  FE67 DA2D 0ACA FC5E 3F75  D6F6 3A0D 8AA0 60F4 1216

"Every program in development at MIT expands until it can read mail."
                                                              -- Unknown