Re: Isnumeric function?

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:josh(at)agliodbs(dot)com]
Sent: Friday, 10 September 2004 4:46 AM
To: Theo Galanakis; pgsql-sql(at)postgresql(dot)org
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.