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
Naoko Reeves naokoree...@gmail.com writes:
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
Tom,
Thank you for your quick reply. Data start with (123 only returns 28
records where as phone number start with[123 returns 1.
Changed the data so that both will return 1 row.
One with (999 query takes about 30 seconds (30983ms) without index.
One with [999 take about 28 ms with index.
Yes,
On Aug 11, 2011, at 18:26, Naoko Reeves naokoree...@gmail.com wrote:
Hello,
I have query phone number in database as follows:
[123) 456-7890
(123) 456-7890
Store phone numbers without formatting...the data is the numbers themselves the
formatting is presentation.
When I query like
Naoko Reeves naokoree...@gmail.com writes:
Also forgot to mentioned the version:
select version() PostgreSQL 8.4.6 on i386-apple-darwin, compiled by GCC
i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370),
Oh --- there's your problem. In 8.4 and earlier, we don't trust
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
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} )$'
