Re: Regex Query Index question

Now that I read more closely the alternation is actually concatenation. My point still stands but your issue is that you have not created a functional index on the decryption result of the encrypted phone number. PostgreSQL does not know that the decrypted phone number is equivalent to the unencrypted field. It only can look at expressions to determine whether an index is usable - not values. A table can have more than one index.

David J.

On Aug 11, 2011, at 19:58, David Johnston <polobo(at)yahoo(dot)com> wrote:

>
>>
>> Not testing here but... and ignore whitespace
>>
>> '^( [ \[\( ]? \s* \d{3} \s* [ -\s\]\) ] \d{3} [ -\s ] \d{4} )$'
>>
> Some tweaks needed but seriously consider dropping RegEx and going the functional index route.
>
>> '^( [ \[\( ]? \s* \d{3} \s* [ -\s\]\) ] \s* \d{3} \s* [ -\s ] \s* \d{4} )$'
>
> Added some extra white-space checking but again not tested.
>
> You can probably find better/more flexible expressions online.
>
> David J.