[Rt-devel] SearchBuild and case insensitive matching on postgres

[Palle Girgensohn]

Hi,

I've modified SearchBuilder/Handle/Pg.pm according to the enclosed diff, 
which makes use of lower() instead of ilike. This will make a difference: 
postgresql will use indices created with "on (lower(field))" when lower is 
used explicitally in the query. ILIKE is not the same thing as lower() = 
xx, though, since many strange things occur with locales. Hence ILIKE never 
uses indices, it just cannot logically do this. (A postgresql developer had 
this to say: "It's a big leap to assume that ILIKE is equivalent to LIKE on 
lower().  Think about Turkish i/I, German esstet (did I spell that right?), 
ch in various languages, etc etc." )

Just for reference:

--On tisdag, mars 30, 2004 14.46.11 -0500 Vivek Khera <vivek at khera.org> 
wrote:
>
> On Mar 30, 2004, at 2:38 PM, Jesse Vincent wrote:
>
>> What is the right way to do fast case-insensitive comparisons in
>> postgres that take database indexes into account?
>
> make your index functional on lower(column) and then compare
> lower(column) = lower('string').  don't just index on the column directly.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Pg.diff
Type: application/octet-stream
Size: 786 bytes
Desc: not available
Url : http://lists.bestpractical.com/pipermail/rt-devel/attachments/20040514/7c029e0b/Pg.obj