Re: Case Insensitive Queries

On Wed, 30 May 2001, Tom Lane wrote:

> Mark <mark@zserve.com> writes:
> > It appears that the behavior of a bpchar compare with a string literal
> > is not implicitly trimming the bpchar before the compare, which IMHO is
> > incorrect behavior.  Is my opinion valid?
> 
> regression=# create table foo (f1 char(20));
> CREATE
> regression=# insert into foo values ('zz');
> INSERT 800569 1
> regression=# select * from foo;
>           f1
> ----------------------
>  zz
> (1 row)
> 
> regression=# select * from foo where f1 = 'zz';
>           f1
> ----------------------
>  zz
> (1 row)
> 
> regression=#
> 
> You'll need to be more specific about what you're unhappy about.

Given the thread, I think the problem he's having is tied up in
upper and lower implicitly converting to text.

select * from foo where upper(f1)='ZZ';
gives no rows but if you put 18 spaces after the ZZ you get the
row.