Josh,
I agree with the machete technique, unfortunately The structure is inplace and a work-around was required.
I created the Index you specified, however it chooses to run a seq scan on the column rather than a Index scan. How can you force it to use that Index..
CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~ '^[0-9]{1,9}$';
select * from botched_table where content = 200::integer
Theo
-----Original Message-----
From: Josh Berkus [mailto:[EMAIL PROTECTED]]
Sent: Friday, 10 September 2004 4:46 AM
To: Theo Galanakis; [EMAIL PROTECTED]
Subject: Re: [SQL] Isnumeric function?
Theo,
> Does anyone have any better suggestions???
Well, one suggestion would be to take a machete to your application. Putting
key references and text data in the same column? Sheesh.
If that's not an option, in addition to the approach you've taken, you could
also do a partial index on the appropriate numeric values:
CREATE INDEX idx_content_numeric ON botched_table(content) WHERE content ~ '^[0-9]{1,9}$';
However, this approach may be more/less effective that the segregation
approach you've already taken.
--
Josh Berkus
Aglio Database Solutions
San Francisco
| ______________________________________________________________________ This email, including attachments, is intended only for the addressee and may be confidential, privileged and subject to copyright. If you have received this email in error, please advise the sender and delete it. If you are not the intended recipient of this email, you must not use, copy or disclose its content to anyone. You must not copy or communicate to others content that is confidential or subject to copyright, unless you have the consent of the content owner. |
