Re: Isnumeric function?

Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au> writes:

> 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

You need to put a "and content ~ '^[0-9]{1,9}$'" in your query, the clause has
to match the clause in the partial index pretty closely.

perhaps you would find it convenient to make a view ofselect * from botched_table where content ~ '^[0-9]{1,9}$'
and then just always select these values from that view.

Also the "::integer" is useless. It actually gets cast to text here anyways.
The index is on the text contents of the content column.

You might consider making the index a functional index on content::integer
instead. I suspect that would be faster and smaller than an index on the text
version of content:

slo=> create table botched_table (content text);
CREATE TABLE
slo=> create index idx_botched_table on botched_table ((content::integer)) where content ~ '^[0-9]{1,9}$';
CREATE INDEX
slo=> create view botched_view as (select content::integer as content_id, * from botched_table where content ~
'^[0-9]{1,9}$');
CREATE VIEW
slo=> explain select * from botched_view where content_id = 1;                                      QUERY PLAN
                            
 
----------------------------------------------------------------------------------------Index Scan using
idx_botched_tableon botched_table  (cost=0.00..3.72 rows=3 width=32)  Index Cond: ((content)::integer = 1)  Filter:
(content~ '^[0-9]{1,9}$'::text)
 
(3 rows)


-- 
greg