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

[Jesse Vincent]

On Thu, May 13, 2004 at 09:38:12PM -0400, Jesse Vincent wrote:
> 
> 
> 
> 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.

So, to be more exact, RT 3.0 doesn't have the infrastructure needed to
let us do LOWER('query') = LOWER('colname') so we fake it with lc. When
talking to newer RT branches, we do the right thing. I've just released
this version of SearchBuilder as 1.00_01. I'd be thrilled if you could
test it out and tell us how you do. If there are other new indexes that
you find to help, I'll add them to the core.

	Best,
	Jesse