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

[Jesse Vincent]

On Fri, May 14, 2004 at 03:05:31AM +0200, Palle Girgensohn wrote:
> 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." )
 
You want to look at the latest development version of
DBIx::SearchBuilder::Handle::Pg, as it has a slightly more elegant
version of what you're trying to do here.


> 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.


> _______________________________________________
> Rt-devel mailing list
> Rt-devel at lists.bestpractical.com
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel


--