ILIKE

I am not familiar with ILIKE, but I suspect that if people are moving
from a platfrom on which it exists, or even creatingmulti-platform
applications, there may be a substancial amount of code that may use it.

Peter Eisentraut wrote:

On Sat, 22 Feb 2003, mlw wrote:

I am not familiar with ILIKE, but I suspect that if people are moving
from a platfrom on which it exists, or even creatingmulti-platform
applications, there may be a substancial amount of code that may use it.

I don't know about other platforms but I've been using it in scripts for
a couple of years.

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.

mlw writes:

I am not familiar with ILIKE, but I suspect that if people are moving
from a platfrom on which it exists, or even creatingmulti-platform
applications, there may be a substancial amount of code that may use it.

But there are no other systems on which it exists.

--
Peter Eisentraut peter_e@gmx.net

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

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

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

Josh Berkus writes:

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

Which database would that be?

--
Peter Eisentraut peter_e@gmx.net

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

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.

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

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.

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

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

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.

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

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

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

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

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

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

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

I don't understand why you would want to remove a working feature. Even
if they are features which you do not like, why remove them? One of the
things about the PostgreSQL core team that troubles me is a fairly
arbitrary feature selection process.

It seems a feature has to be "liked" by someone for inclusion. I am
often taken by surprise by how you guys judge what the PostgreSQL
usership wants or "needs" based on your own perspective, and if someone
uses it differently, the reaction is fierce resistance.

The issue seems to be that there is some sort of feature phobia. Why
remove "ILIKE?" Why not just document an alternative for higher
performance? Why can't you guys allow features even though you don't
necessarily agree? Yes, absolutely, assure the quality and accuracy of
the feature, but just ease up on the resistance. Allow things even
though you don't see the usefulness. Keep features even though you don't
agree with them.

One of the benefits of open source is the inclusiveness of contribution.
The plurality of development. The ability to harness the experience and
work of people around the world. People with different objectives and
perspectives than yours.

In Open Source, the attitude should not be "do we want this feature?"
but "can we add/keep this without affecting anything else?" The first
argument is based on the assumption you know what everyone wants or
needs, which is preposterous, the second argument is based on how well
you know the PostgreSQL code and structure, which is a far more
reasonable position.

On Tue, Feb 25, 2003 at 08:13:27AM -0500, mlw wrote:

things about the PostgreSQL core team that troubles me is a fairly
arbitrary feature selection process.

[. . .]

In Open Source, the attitude should not be "do we want this feature?"
but "can we add/keep this without affecting anything else?" The first

I can't think of an actual case where PostgreSQL dropped a feature
without the latter question being the one which was answered. Note
that one possible value of "anything else" in that question is
"ability to work on something else instead of maintaining this code".
Sometimes features get dropped because no-one is interested in
maintaining them (where "interest" is measured as a function of
willingness to do the maintenance on the code), and the cost of
maintaining them is great enough that it's a distraction.

That said, it seems to me even the latter case is pretty rare. What
case were you thinking of? (Surely this one doesn't qualify as an
example: it's apparent that the suggestion to remove ILIKE has caused
plenty of opposition.)

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

I can comment on this --- adding a feature isn't zero cost. There is
maintenance, but the larger cost is of users wading through features to
figure out if they need it or not. We don't want to bloat ourselves to
the point PostgreSQL becomes harder to use.

Let's face it, you have to understand a feature before you can decide if
it useful to you. Adding a feature that is of limited usefulness pushes
that analysis on every PostgreSQL users studying the PostgreSQL feature
set.

---------------------------------------------------------------------------

mlw wrote:

I don't understand why you would want to remove a working feature. Even
if they are features which you do not like, why remove them? One of the
things about the PostgreSQL core team that troubles me is a fairly
arbitrary feature selection process.

It seems a feature has to be "liked" by someone for inclusion. I am
often taken by surprise by how you guys judge what the PostgreSQL
usership wants or "needs" based on your own perspective, and if someone
uses it differently, the reaction is fierce resistance.

The issue seems to be that there is some sort of feature phobia. Why
remove "ILIKE?" Why not just document an alternative for higher
performance? Why can't you guys allow features even though you don't
necessarily agree? Yes, absolutely, assure the quality and accuracy of
the feature, but just ease up on the resistance. Allow things even
though you don't see the usefulness. Keep features even though you don't
agree with them.

One of the benefits of open source is the inclusiveness of contribution.
The plurality of development. The ability to harness the experience and
work of people around the world. People with different objectives and
perspectives than yours.

In Open Source, the attitude should not be "do we want this feature?"
but "can we add/keep this without affecting anything else?" The first
argument is based on the assumption you know what everyone wants or
needs, which is preposterous, the second argument is based on how well
you know the PostgreSQL code and structure, which is a far more
reasonable position.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073