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