Skip site navigation (1) Skip section navigation (2)

Re: ILIKE

From: "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: ILIKE
Date: 2003-02-24 16:34:14
Message-ID: Pine.LNX.4.33.0302240915100.1816-100000@css120.ihs.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Sat, 22 Feb 2003, Peter Eisentraut wrote:

> AFAICT, ILIKE cannot use an index.  So why does ILIKE even exist, when
> lower(expr) LIKE 'foo' provides a solution that can use an index and is
> more standard, too?

I would guess because for lower(expr) to work you need to make an index on 
it.  Since making ilike work invisibly would require the creation of an 
"invisible" lower(expr) index, it would double index storage requirements 
without warning the user.

To make ilike invisible it might be worth setting up a GUC that controls 
automatic ilike index creation.  That way ilike could either be a seq scan 
all the time function, which is great for certain operations anyway, or
an automatically indexed operation.  

#create_ilike_indexes = false  # costs 2x storage on index of text, char, 
types

I like ilike, but it's seq scan nature is a bit klunky.


In response to

  • ILIKE at 2003-02-22 00:36:43 from Peter Eisentraut

pgsql-hackers by date

Next:From: Neil ConwayDate: 2003-02-24 16:37:49
Subject: Re: REPEATED INSERT INTO ...
Previous:From: Shridhar Daithankar<shridhar_daithankar@persistent.co.in>Date: 2003-02-24 15:53:38
Subject: Thread safe ecpg

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group