Regex Query Index question

From: Naoko Reeves <naokoreeves(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Regex Query Index question
Date: 2011-08-11 22:26:21
Message-ID: CAGoos17T=Ch9zqxrEDjDSx_PVOTP-3w44n7GvqoKL3XZ9jtdmg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
I have query phone number in database as follows:

[123) 456-7890

(123) 456-7890

When I query like this:

SELECT * FROM phone

WHERE phone_number ~ ('^\[123' || '[-\s\)]{0,2}' || '456' || '[-\s\)]{0,2}'
|| '7890')

it use Index but if I query like this (notice first character is
open parenthesis instead of open square blacket ) :

SELECT phn_fk_key FROM phn WHERE

phn_fk_table = 14

AND llx_decrypt(phn_phone_enc) ~ ('^\(123' || '[-\s\)]{0,2}' || '456' ||
'[-\s\)]{0,2}' || '7890')

It doesn't use Index....

co-worker suggested me to use chr(40) instead so I tried this:

SELECT phn_fk_key FROM phn WHERE

phn_fk_table = 14

AND llx_decrypt(phn_phone_enc) ~ ('^\' || chr(40) || '123' || '[-\s\)]{0,2}'
|| '456' || '[-\s\)]{0,2}' || '7890')

No success...

Also { and period doesn't seems to use index either.... but } ) [ ] $ #
works.

Could you guide me to right direction for me please?

Thank you very much for your time in advance.

Naoko Reeves

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marc Munro 2011-08-11 22:45:59 Re: [HACKERS] Dropping extensions
Previous Message John DeSoi 2011-08-11 21:57:57 suggestions for archive_command to a remote standby