On Wed, Nov 19, 2008 at 12:50:23PM +0200, Andrus wrote:
> Sam,
> > SELECT MAX(nullif(regexp_replace(test, '^([0-9]*).*$', E'\\1'),'')::INT);
>
> Thank you.
> This seems to work but is bit slow.
It will have to be executed against every row before you get an answer,
so if you're just after the max of a whole table will be pretty slow.
> How to speed it up ?
> Should I create index
>
> CREATE INDEX test ON test ( nullif(regexp_replace(test, '^([0-9]*).*$',
> E'\\1'),'')::INT );
that would work. I'd be tempted to use the substring() function instead
as it looks a bit prettier (peformance seems indistuinguishable). So
I'd use the following pair:
CREATE INDEX test_test_idx ON test
((nullif(substring(test, '^[0-9]*'),'')::int));
and
SELECT MIN(nullif(substring(test, '^[0-9]*'),'')::int) FROM test;
you could use a view as well, at which point you wouldn't have to
remember how you were converting the string into an int:
CREATE VIEW test_v AS
SELECT *, nullif(substring(test, '^[0-9]*'),'')::int AS test_int
FROM test;
allowing a simple:
SELECT MIN(test_int) FROM test_v;
hope that helps!
Sam
