Re: Isnumeric function?

O Theo Galanakis έγραψε στις Sep 10, 2004 :

> 
> 
> 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

EXPLAIN ANALYZE is your friend.
VACUUM [FULL] ANALYZE also.
Try with 200::text
In the end if there is an option for the planner to use the index
but he doesn't, then maybe its not worth it.

> 
> Theo
> -----Original Message-----
> From: Josh Berkus [mailto:josh@agliodbs.com] 
> Sent: Friday, 10 September 2004 4:46 AM
> To: Theo Galanakis; pgsql-sql@postgresql.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.
> 
> 

-- 
-Achilleus