Re: matching column of regexps

>>>>> "VF" == Vik Fearing <vik(dot)fearing(at)dalibo(dot)com> writes:

JC>> Is there a better way to answer the question, "Do ANY rows match?"

VF> select exists (select 1 from retest where active is true and ? ~ re);

Ah. Yes. I'd forgotten about select exists. I cannot recall whether I
ever used it in anger, or just played around after reading about it.

It should stick this time.

>> Is there a way to index such a table/query?

VF> There are several ways to index such a query. If there are very many
VF> rows but with only a few being active, then a partial index will do wonders.

Its more likely only a few will be inactive.

VF> Otherwise, it is possible to use an index for regular expressions using
VF> the pg_trgm extension.

Perfect. I see trgm index support for ~, et alia is new in 9.3.

Exactly the kicks in the skull I needed.

Thanks,

-JimC
--
James Cloos <cloos(at)jhcloos(dot)com> OpenPGP: 0x997A9F17ED7DAEA6