Re: matching column of regexps

>>>>> "VF" == Vik Fearing <vik.fearing@dalibo.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@jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6