Re: Full Text Index Scanning

I was looking for an answer to the same problem posted a while back (sorry,
not sure how to join that thread):

Thanks. pg_trgm looks interesting, but after installing the pg_trgm.sql, I
get error messages when following the documentation.

sggeeorg=> create index test_idx on test using gist(columnname
gist_trgm_ops);
ERROR: operator class "gist_trgm_ops" does not exist for access method
"gist"
STATEMENT: create index test_idx on test using
gist(columnname gist_trgm_ops);
ERROR: operator class "gist_trgm_ops" does not exist for access method
"gist"

On Sun, Jan 30, 2011 at 10:36 AM, Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Matt Warner <matt(at)warnertechnology(dot)com> writes:
> > If I understand this, it looks like this approach allows me to match the
> > beginnings and endings of words, but not the middle sections.
>
> Yeah, probably. You might consider using contrib/pg_trgm instead if
> you need arbitrary substrings.
>
> regards, tom lane
>

I just ran into the same problem using pgsql 9.1 on both Windows and
Linux. It happened when I tried
to create an index on a new table in a new schema of a database that
already had the pg_trgm extension
loaded. The only way I could get the index to build was to first drop
the extension and then re-add it.
Once I re-added the extension, I was then able to create the index.