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


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: (view raw, whole thread or download thread mbox)
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, 

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
Previous:From: Shridhar Daithankar<>Date: 2003-02-24 15:53:38
Subject: Thread safe ecpg

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