"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