Peter,

Several reasons (because I like lists):
- Some other databases support ILIKE and it makes porting easier.
- For tables and/or subqueries that are too small to need an index, ILIKE is 
perfectly acceptable.
- It's also useful for comparing expressions, and is faster to type than
	'jehosaphat' ~* '^Jehosaphat$', and certainly much faster than
	lower('jehosaphat') = lower('Jehosaphat')

Why this sudden urge to prune away perfectly useful operators?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Sun, 23 Feb 2003 13:02:27 -0800
Josh Berkus <josh@agliodbs.com>
Re: ILIKE
Josh Berkus <josh@agliodbs.com> writes:
> - Some other databases support ILIKE and it makes porting easier.

Which other ones?  I checked our archives and found that when we were
discussing adding ILIKE, it was claimed that Oracle had it.  But I can't
find anything on the net to verify that claim.  I did find that mSQL
(not MySQL) had it, as far back as 1996.  Nothing else seems to --- but
Google did provide a lot of hits on pages saying that ILIKE is a mighty
handy Postgres-ism ;-)

> Why this sudden urge to prune away perfectly useful operators?

My feeling too.  Whatever you may think of its usefulness, it's been a
documented feature since 7.1.  It's a bit late to reconsider.

			regards, tom lane

Sun, 23 Feb 2003 23:31:22 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: ILIKE 
On Sun, 2003-02-23 at 23:31, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> > - Some other databases support ILIKE and it makes porting easier.
> 
> Which other ones?  I checked our archives and found that when we were
> discussing adding ILIKE, it was claimed that Oracle had it.  But I can't
> find anything on the net to verify that claim.  I did find that mSQL
> (not MySQL) had it, as far back as 1996.  Nothing else seems to --- but
> Google did provide a lot of hits on pages saying that ILIKE is a mighty
> handy Postgres-ism ;-)

Isn't MySQL case insensitive by default?  I know the ='s operator is
(was?)

'a' = 'A'

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
24 Feb 2003 07:04:46 -0500
Rod Taylor <rbt@rbt.ca>
Re: ILIKE
Josh Berkus writes:

> - Some other databases support ILIKE and it makes porting easier.

Which database would that be?

-- 
Peter Eisentraut   peter_e@gmx.net



Mon, 24 Feb 2003 13:30:18 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: ILIKE
Tom Lane writes:

> My feeling too.  Whatever you may think of its usefulness, it's been a
> documented feature since 7.1.  It's a bit late to reconsider.

It's never too late for new users to reconsider.  It's also never too late
to change your application of performance is not satisfactory.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 24 Feb 2003 13:32:44 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: ILIKE 
On Mon, 24 Feb 2003, Peter Eisentraut wrote:

> Tom Lane writes:
>
> > My feeling too.  Whatever you may think of its usefulness, it's been a
> > documented feature since 7.1.  It's a bit late to reconsider.
>
> It's never too late for new users to reconsider.  It's also never too late
> to change your application of performance is not satisfactory.

And if performance is satisfactory?

Vince.
-- 
 Fast, inexpensive internet service 56k and beyond!  http://www.pop4.net/
   http://www.meanstreamradio.com       http://www.unknown-artists.com
         Internet radio: It's not file sharing, it's just radio.


Mon, 24 Feb 2003 07:38:34 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: ILIKE 
Peter Eisentraut wrote:
> Tom Lane writes:
> 
>>My feeling too.  Whatever you may think of its usefulness, it's been a
>>documented feature since 7.1.  It's a bit late to reconsider.
> 
> It's never too late for new users to reconsider.  It's also never too late
> to change your application of performance is not satisfactory.
> 

Well, ILIKE has been a feature for quite some time and the amount of 
negative feedback we've been receiving about upgrade problems makes me 
feel that _removing_ it would be detrimental.  (i.e. broken applications)

As an alternative to _removing_ it, would a feasible idea be to 
transparently alias it to something else, say a specific type of regex 
query or something?

Regards and best wishes,

Justin Clift

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi


Tue, 25 Feb 2003 01:09:20 +1030
Justin Clift <justin@postgresql.org>
Re: ILIKE
On Tue, 25 Feb 2003, Justin Clift wrote:

> Peter Eisentraut wrote:
> > Tom Lane writes:
> >
> >>My feeling too.  Whatever you may think of its usefulness, it's been a
> >>documented feature since 7.1.  It's a bit late to reconsider.
> >
> > It's never too late for new users to reconsider.  It's also never too late
> > to change your application of performance is not satisfactory.
> >
>
> Well, ILIKE has been a feature for quite some time and the amount of
> negative feedback we've been receiving about upgrade problems makes me
> feel that _removing_ it would be detrimental.  (i.e. broken applications)
>
> As an alternative to _removing_ it, would a feasible idea be to
> transparently alias it to something else, say a specific type of regex
> query or something?

Why screw with it for the sake of screwing with it?

Vince.
-- 
 Fast, inexpensive internet service 56k and beyond!  http://www.pop4.net/
   http://www.meanstreamradio.com       http://www.unknown-artists.com
         Internet radio: It's not file sharing, it's just radio.


Mon, 24 Feb 2003 10:13:06 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: ILIKE
Vince Vielhaber <vev@michvhf.com> writes:
> On Tue, 25 Feb 2003, Justin Clift wrote:
>> As an alternative to _removing_ it, would a feasible idea be to
>> transparently alias it to something else, say a specific type of regex
>> query or something?

> Why screw with it for the sake of screwing with it?

AFAICT, Peter isn't interested in changing the implementation, but in
removing it outright (to reduce our nonstandardness, or something like
that).  While we've removed marginal features in the past, I think this
one is sufficiently popular that there's no chance of removing it just
on the strength of the argument that it's not standard.

The efficiency argument seemed irrelevant --- AFAICT, ILIKE is exactly
as indexable as any equivalent regex substitute, which is to say
"only if the pattern's leading characters are fixed (nonalphabetic)".

			regards, tom lane

Mon, 24 Feb 2003 10:24:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: ILIKE 
Vince Vielhaber wrote:
<snip>
> Why screw with it for the sake of screwing with it?

Hmmm, good point.  "If it aint broke" ?

Regards and best wishes,

Justin Clift


> Vince.

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi


Tue, 25 Feb 2003 01:59:01 +1030
Justin Clift <justin@postgresql.org>
Re: ILIKE
Vince Vielhaber writes:

> > It's never too late for new users to reconsider.  It's also never too late
> > to change your application of performance is not satisfactory.
>
> And if performance is satisfactory?

Hey, I don't want to take your ILIKE away.  But at the time it was added
the claim was that it was for compatibility and now we learn that that was
wrong.  That is something to make people aware of, for example in the
documentation.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 24 Feb 2003 18:02:02 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: ILIKE 
Peter Eisentraut <peter_e@gmx.net> writes:
> Hey, I don't want to take your ILIKE away.  But at the time it was added
> the claim was that it was for compatibility and now we learn that that was
> wrong.  That is something to make people aware of, for example in the
> documentation.

It already does say

: The keyword ILIKE can be used instead of LIKE to make the match case
: insensitive according to the active locale. This is not in the SQL
: standard but is a PostgreSQL extension.

What else would you want to say?

			regards, tom lane

Mon, 24 Feb 2003 12:30:44 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: ILIKE 
Four Reasons to use ILIKE, which have nothing to do with mSQL:

1) It's faster to type than most analagous regexp comparisons, and much faster 
than comparing two LOWERs or two UPPERS.

2) It's a great operator for comparing two text variables or columns of small 
tables where you don't want to worry about escaping the many items of regexp 
punctuation.

3) It's an easy search-and-replace operator for porting applications from SQL 
databases which automatically do case-insensitive comparisons using LIKE, 
such as MySQL and some installations of MSSQL.

4) It's just as indexible (or not indexable) as regexp comparisons, and easier 
to understand for users from the Microsoft world than regexp.

And, on a quick search, one of my applications uses ILIKE 21 times in the 
built in functions and views.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Mon, 24 Feb 2003 10:39:15 -0800
Josh Berkus <josh@agliodbs.com>
Re: ILIKE
Josh Berkus writes:

> 4) It's just as indexible (or not indexable) as regexp comparisons, and easier
> to understand for users from the Microsoft world than regexp.

ILIKE is not indexible at all.  Other forms of pattern comparisons are at
least indexible sometimes.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 24 Feb 2003 20:19:34 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: ILIKE
Peter,

> > 4) It's just as indexible (or not indexable) as regexp comparisons, and
> > easier to understand for users from the Microsoft world than regexp.
>
> ILIKE is not indexible at all.  Other forms of pattern comparisons are at
> least indexible sometimes.

And how is  ~*  indexable?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Mon, 24 Feb 2003 11:22:08 -0800
Josh Berkus <josh@agliodbs.com>
Re: ILIKE
Peter Eisentraut <peter_e@gmx.net> writes:
> Josh Berkus writes:
>> 4) It's just as indexible (or not indexable) as regexp comparisons, and easier
>> to understand for users from the Microsoft world than regexp.

> ILIKE is not indexible at all.

You are arguing from a false premise.

regression=# create table foo (f1 text unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'foo_f1_key' for table 'foo'
CREATE TABLE
regression=# explain select * from foo where f1 ilike '123%';
                               QUERY PLAN
------------------------------------------------------------------------
 Index Scan using foo_f1_key on foo  (cost=0.00..17.07 rows=5 width=32)
   Index Cond: ((f1 >= '123'::text) AND (f1 < '124'::text))
   Filter: (f1 ~~* '123%'::text)
(3 rows)

ILIKE is exactly as indexable as any other pattern that does the same
thing.

			regards, tom lane

Mon, 24 Feb 2003 16:25:51 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: ILIKE 
Tom Lane kirjutas E, 24.02.2003 kell 19:30:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Hey, I don't want to take your ILIKE away.  But at the time it was added
> > the claim was that it was for compatibility and now we learn that that was
> > wrong. 

This _is_ a compatibility feature, just not as straightforward as you
may think, i.e. some databases have LIKE which behaves like our ILIKE.

>  That is something to make people aware of, for example in the
> > documentation.
> 
> It already does say
> 
> : The keyword ILIKE can be used instead of LIKE to make the match case
> : insensitive according to the active locale. This is not in the SQL
> : standard but is a PostgreSQL extension.
> 
> What else would you want to say?

Perhaps add (From the mail of Josh Berkus):

3) It's an easy search-and-replace operator for porting applications
from SQL databases which automatically do case-insensitive comparisons
using LIKE, such as MySQL and some installations of MSSQL.


---------------
Hannu


25 Feb 2003 00:20:37 +0200
Hannu Krosing <hannu@tm.ee>
Re: ILIKE