On Sat, Dec 29, 2012 at 2:05 PM, Greg Stark 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 f
On Fri, Dec 28, 2012 at 11:41 PM, Tom Lane wrote:
> James Cloos 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 actua
> "TL" == Tom Lane 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> questio
James Cloos 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
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
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
"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.
On 3/18/07, Martijn van Oosterhout 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. Th
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 hardcode
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)
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
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 anythin
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
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
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=
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?
--
Jo
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
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 wor
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 d
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
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. Si
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 wi
Vince Vielhaber wrote:
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
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 al
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
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
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.postgr
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.
--
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
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.
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
'jeh
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 Eisentr
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
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 ex
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 sea
35 matches
Mail list logo
