Re: Using regular expressions in LIKE - Mailing list pgsql-general

From Tom Lane
Subject Re: Using regular expressions in LIKE
Date
Msg-id 22128.1074179741@sss.pgh.pa.us
Whole thread Raw
In response to Re: Using regular expressions in LIKE  ("Nick Barr" <nick.barr@webbased.co.uk>)
Responses Re: Using regular expressions in LIKE  (Karsten Hilbert <Karsten.Hilbert@gmx.net>)
List pgsql-general
"Nick Barr" <nick.barr@webbased.co.uk> writes:
>> Isn't there also a performance benefit as you can use an index if you
>> say "this definitely starts at the beginning" with the '^'?

> That is what I was thinking, which is the other reason why I put it in.
> This is only the case with the default locale I believe. I have no idea
> when it comes to regexs though and specifically the ~ operator. Could
> someone more knowledgeable about this stuff reply?

Regexes are optimized the same way as equivalent LIKE expressions.  In
particular, the pattern has to be left-anchored to consider using it
with an index.  In LIKE that means no wildcard at the start of the
pattern, in regex it means there has to be a ^.

Locale and case sensitivity issues are the same, too.

            regards, tom lane

pgsql-general by date:

Previous
From: David Helgason
Date:
Subject: Re: YAGT (yet another GUID thread)
Next
From: "Marc G. Fournier"
Date:
Subject: Re: Mailing list? was Postgress and MYSQL