Re: Case Insensitive Queries

On 30 May 2001, Mark wrote:

> On 30 May 2001 11:16:35 -0700, Stephan Szabo wrote:
> > 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.
> >
> >
>
>
> could I cast from text to something else?

You might be able to get away with something like:
create function upper(char) returns char as 'upper'
language 'internal';

It seems to work for me, but I'm not 100% sure how
safe it is.

With the function above,
select * from foo where upper(f1)='ZZ' returns me the zz
row.