Re: Using regular expressions in LIKE

"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