Re: uppercase = lowercase

On Fri, 14 Feb 2003, Ken Guest wrote:

> Richard Huxton wrote:
>
> >On Friday 14 Feb 2003 8:51 am, jose antonio leo wrote:
> >
> >
> >>Hi!!
> >>
> >>How can I make selects not sensitive uppercase and lowercase characters?
> >>This is possible modifying something of psql configuration?
> >>
> >>
> >
> >This isn't possible in a general way. If you want "Richard","RICHARD" and
> >"riCHard" to all test the same you'll need to do something like.
> >
> >SELECT * FROM people WHERE lower(first_name)='richard';
> >
> >You can create an index on lower(first_name) if you need to speed things
> >along.
> >
>
>
> I thought you could only create indices on fields - not on the results
> of operations on those fields.
> Is it truly possible to create an index on lets say upper(last_name)?

Yes, these are called functional indexes.  The only caveat is that the
arguments must all be columns, not constants.

So,

create index bubbahotep on pyramids (substr(col1,0,4));

will fail, but

update pyramids set col2=0,col3=4;
create index test on pyramids (substr(col1,col2,col3));
select * from pyramids where substr(col1,col2,col3) = 'abcd';

will work.

If you didn't know about them, then you probably don't know about partial
indexes either, very useful.  let's say you have a table where 99.9% of
all rows have the boole field approved marked true.  You can create a
small index on the false ones like so:

create index test on articles (approved) where approved is false;

then

select * from  articles where approved is false

should return quickly.  Note that the parts of the where clause pretty
much need to be identical, i.e.

select * from articles where approved is not true;
select * from articles where approved !='t';
select * from aticles where approved ='f';

will not use that index, since they aren't the same suntax.  Plus some of
those aren't really equivalent, given nulls in you data set.