Re: Case Insensitive Queries

On Wed, 30 May 2001, Tom Lane wrote:

> Mark <mark(at)zserve(dot)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.