| 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: | Whole Thread | Raw Message | 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
| 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 |