Re: [HACKERS] ILIKE vs indices
TL == Tom Lane t...@sss.pgh.pa.us writes:
JC Is there any contraindication to recasting:
JC foo ILIKE 'bar'
JC into:
JC LOWER(foo) LIKE LOWER('bar')
TL In some locales those are not equivalent, I believe, or at least
TL shouldn't be. (What the current code actually does is a separate
TL question.)
I see. After determining indexing based on th existance of an initial
fixed string, exluding anything matching isalpha(), it uses tolower(3)
and friends to do the actual match.
So my proposal wouldn't change what matches, but might make fixing any
bugs in what *should* match more difficult?
TL In any case it's not obvious why LOWER rather than UPPER.
I suggested lower() because that matched all of the suggestions I found.
And as it turns out matches the current behaviour, too.
The footnote about adding explicit mention to the docs was expressly
because it is not otherwise obvious whether indices should use lower()
or upper().
I'll ask on one of the unicode lists whether there are any locales where
a case-insensitive match should be different than a case-preserving match
of tolower() vs tolower().
-JimC
--
James Cloos cl...@jhcloos.com OpenPGP: 1024D/ED7DAEA6
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ILIKE vs indices
On Fri, Dec 28, 2012 at 11:41 PM, Tom Lane t...@sss.pgh.pa.us wrote:
James Cloos cl...@jhcloos.com writes:
Is there any contraindication to recasting:
foo ILIKE 'bar'
into:
LOWER(foo) LIKE LOWER('bar')
In some locales those are not equivalent, I believe, or at least
shouldn't be. (What the current code actually does is a separate
question.)
What it actually does is actually *precisely* the above.
I can't quite wrap my head around the idea of LIKE and collations
having any meaningful interaction anyways. I certainly can't come up
with anything better than lower() like lower() (or upper() like
upper()).
It would be nice to document what ILIKE actually means. Right now it's
kind of mysterious. And if we can't come up with anything better than
lower() like lower() then why not go ahead and document it and take
advantage of it.
--
greg
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ILIKE vs indices
On Sat, Dec 29, 2012 at 2:05 PM, Greg Stark st...@mit.edu wrote: I can't quite wrap my head around the idea of LIKE and collations having any meaningful interaction anyways. I certainly can't come up with anything better than lower() like lower() (or upper() like upper()). Hm. Maybe I spoke too fast. Perhaps we should just call strcasecmp() character by character, or even call strcasecmp() on any substring of the pattern that doesn't contain _ or % ? The latter would be pretty hopeless to ever use a btree index though. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ILIKE vs indices
While tuning an application, I found the posts from 2003 recomending the
use of LOWER() and LIKE in place of ILIKE to take advantage of indices.
For this app, given the limitations of the upper-layer protocol it must
support, that change replaced about 30 minutes of repeated seq scans with
about 1 minute of repeated index scans! On a query-set often repeated
several times per day. (Probably more times per day now.)
Is there any contraindication to recasting:
foo ILIKE 'bar'
into:
LOWER(foo) LIKE LOWER('bar')
and documenting that an index has to be on LOWER(column) to benefit ILIKE?
Perhaps the parser could read the former as the latter?
-JimC
--
James Cloos cl...@jhcloos.com OpenPGP: 1024D/ED7DAEA6
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ILIKE vs indices
James Cloos cl...@jhcloos.com writes:
Is there any contraindication to recasting:
foo ILIKE 'bar'
into:
LOWER(foo) LIKE LOWER('bar')
In some locales those are not equivalent, I believe, or at least
shouldn't be. (What the current code actually does is a separate
question.)
Perhaps the parser could read the former as the latter?
Not unless the equivalence can be shown to be exact, which I doubt.
In any case it's not obvious why LOWER rather than UPPER.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ILIKE and indexes
On 3/19/07, Tom Lane [EMAIL PROTECTED] wrote: Not if you have an index on lower(col) which one supposes you'd have anyway for such an application. Or are you running an ancient PG release? Yes, you're right. Looking at my history I can't find what my error was - I analyzed the table several times to be sure. As I was playing with the production db and a 8.2.3 db, perhaps I missed a command on one of the db server. Anyway, the estimates are accurate now. Thanks for your help. -- Guillaume ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] ILIKE and indexes
Hi all, I'm currently facing a common problem with queries using ILIKE: it can't use an index except if the pattern begins with non alpha characters. The usual trick recommended in the doc is to use lower() and LIKE but it leads to bad row estimates (it's constant whatever the search pattern is) and in several use cases we have, it's a real problem because the rows are far from being equally distributed. To take a real life example, if I look for 'c%' or 'l%' patterns in one of my tables, it returns a lot of rows and the nested loop chosen by the planner for every pattern is a very poor choice for these particular patterns. I'd like to see an opclass similar to (text|bpchar|varchar|name)_pattern_ops to deal with ILIKE. I found this post of Jan http://archives.postgresql.org/pgsql-hackers/2003-10/msg01550.php but I'd really like not to introduce a new set of non standard operators to deal with this feature. I have planned to write the operator class as a contrib module but I couldn't find the link between LIKE operator and text_pattern_ops opclass which uses ~=~ and all its variants. Andrew from Supernews told me it was hardcoded in the planner so the introduction of this new opclass requires a few changes to the planner to take it into account for ILIKE. What I'd like to do: * introduce 4 new opclasses called (text|bpchar|varchar|name)_icpattern_ops with ~=~* operator and variants * change the planner to make it use these operators for ILIKE in the same way it is done for LIKE (mostly remove the non alpha limitation and point the planner to the new operators) Is there any fundamental problem in this approach? I mostly wonder if there are any significant problems which prevented us from doing it before and I've missed in my analysis. Is there anything I should particularly take care of? Thanks for any advice or comment. -- Guillaume ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ILIKE and indexes
On Sun, Mar 18, 2007 at 07:30:35PM +0100, Guillaume Smet wrote: I have planned to write the operator class as a contrib module but I couldn't find the link between LIKE operator and text_pattern_ops opclass which uses ~=~ and all its variants. Andrew from Supernews told me it was hardcoded in the planner so the introduction of this new opclass requires a few changes to the planner to take it into account for ILIKE. Er, it's link between LIKE and the ~=~ that's hard coded, however the link between the operator class and the operator is nothing special, that's why it's an operator class. So I think it's easier that you think: just build the operator class and make sure you use the right operator so the planner uses it. ILIKE already maps to an operator... Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] ILIKE and indexes
On 3/18/07, Martijn van Oosterhout kleptog@svana.org wrote: Er, it's link between LIKE and the ~=~ that's hard coded Yes. So I think it's easier that you think: just build the operator class and make sure you use the right operator so the planner uses it. ILIKE already maps to an operator... Yeah I know. The fact is that you can't use an index for any pattern and it depends on the database encoding too. The code in the planner checks that the pattern and the database encoding makes the index usable and rewrites the LIKE clause (and the ILIKE clause if the pattern begins with a non alpha character) so that it can use the index. So I'm pretty sure I have to change this behaviour in the planner or did I miss something? -- Guillaume ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] ILIKE and indexes
Guillaume Smet [EMAIL PROTECTED] writes: The usual trick recommended in the doc is to use lower() and LIKE but it leads to bad row estimates (it's constant whatever the search pattern is) Not if you have an index on lower(col) which one supposes you'd have anyway for such an application. Or are you running an ancient PG release? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ILIKE
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 [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ILIKE
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 [EMAIL PROTECTED])
Re: [HACKERS] ILIKE
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 [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ILIKE
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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] ILIKE
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ILIKE
Tom Lane kirjutas E, 24.02.2003 kell 19:30: Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ILIKE
On Sun, 2003-02-23 at 23:31, Tom Lane wrote: Josh Berkus [EMAIL PROTECTED] 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 [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
Re: [HACKERS] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ILIKE
Josh Berkus writes: - Some other databases support ILIKE and it makes porting easier. Which database would that be? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ILIKE
Vince Vielhaber [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] ILIKE
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ILIKE
Josh Berkus [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ILIKE
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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 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: 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? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ILIKE
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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ILIKE
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] ILIKE
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? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
