Re: Postgres case insensitive searches

Ingmar Brouns wrote:
>> My solution is fast and efficient, it will call upper() only once
>> per query.  I don't see your problem.  Different database systems
>> do things in different ways, but as long as you can do what you need
>> to do, that should be good enough.

> I was toying around a little bit with this example, just for my
> understanding, the function upper is called for every row in the
> result. I think this has something to to with the filter in the plan.

You are right, and the function is also called once per
result row.  The point I was really trying to make is that
it is *not* called once per row in the table.

> postgres=# explain select * from foo where test_upper(md5) like  'ABAAB%';
>                                           QUERY PLAN
> ----------------------------------------------------------------------------------------------
>  Index Scan using foo_ind on foo  (cost=0.50..14.02 rows=250 width=33)
>    Index Cond: ((test_upper(md5) ~>=~ 'ABAAB'::text) AND
> (test_upper(md5) ~<~ 'ABAAC'::text))
>    Filter: (test_upper(md5) ~~ 'ABAAB%'::text)
> (3 rows)
> 
> 
> So under my assumption that it is the filter that causes the function
> execution, I don't understand
> how a row can satisfy
> 
> --which I read as >= 'ABAAB' and < 'ABAAC'
> ((test_upper(md5) ~>=~ 'ABAAB'::text) AND (test_upper(md5) ~<~ 'ABAAC'::text))
> 
> and not
> 
> (test_upper(md5) ~~ 'ABAAB%'::text)

I don't know, but I suspect it has to do with collations.

Yours,
Laurenz Albe