Re: Isnumeric function? - Mailing list pgsql-sql

<br /><p><font size="2">Josh,</font><p><font size="2">I agree with the machete technique, unfortunately The structure
isinplace and a work-around was required.</font><p><font size="2">I created the Index you specified, however it chooses
torun a seq scan on the column rather than a Index scan. How can you force it to use that Index..</font><p><font
size="2">CREATEINDEX idx_content_numeric ON botched_table(content) WHERE content ~ '^[0-9]{1,9}$';</font><p><font
size="2">select* from botched_table where content = 200::integer</font><p><font size="2">Theo</font><br /><font
size="2">-----OriginalMessage-----</font><br /><font size="2">From: Josh Berkus [<a
href="mailto:josh@agliodbs.com">mailto:josh@agliodbs.com</a>]</font><br /><font size="2">Sent: Friday, 10 September
20044:46 AM</font><br /><font size="2">To: Theo Galanakis; pgsql-sql@postgresql.org</font><br /><font size="2">Subject:
Re:[SQL] Isnumeric function?</font><br /><p><font size="2">Theo,</font><p><font size="2">> Does anyone have any
bettersuggestions???</font><p><font size="2">Well, one suggestion would be to take a machete to your application. 
Putting</font><br /><font size="2">key references and text data in the same column?   Sheesh.</font><p><font
size="2">Ifthat's not an option, in addition to the approach you've taken, you could </font><br /><font size="2">also
doa partial index on the appropriate numeric values:</font><p><font size="2">CREATE INDEX idx_content_numeric ON
botched_table(content)WHERE content ~ '^[0-9]{1,9}$';</font><p><font size="2">However, this approach may be more/less
effectivethat the segregation </font><br /><font size="2">approach you've already taken.</font><p><font size="2">--
</font><br/><font size="2">Josh Berkus</font><br /><font size="2">Aglio Database Solutions</font><br /><font
size="2">SanFrancisco</font><table><tr><td bgcolor="#ffffff"><font
color="#000000">______________________________________________________________________<br/>This email, including
attachments,is intended only for the addressee<br />and may be confidential, privileged and subject to copyright. If
you<br/>have received this email in error, please advise the sender and delete<br />it. If you are not the intended
recipientof this email, you must not<br />use, copy or disclose its content to anyone. You must not copy or <br
/>communicateto others content that is confidential or subject to <br />copyright, unless you have the consent of the
contentowner.<br /></font></td></tr></table>