6

I am using a query where the following line written

CREATE INDEX trgm_idx ON public.roads_r USING gin (name gin_trgm_ops);

Its returning the following error

operator class "gin_trgm_ops" does not exist for access method "gin"

How can I overcome this?

2
  • 6
    Have you searched for the answer yourself? The very first hit from Google explained it to me.
    – Scro
    Commented Jun 24, 2013 at 22:46
  • 2
    I'm not sure this is a good question for Geographic Information Systems because it has nothing to do with PostGIS. This is a PostgreSQL question on Trigram indexing, and it's better suited for Database Administrators Commented Jan 9, 2019 at 5:49

4 Answers 4

14

It's part of the PostgreSQL Trigram Index support and it's provided by the pg_trgm extension.

CREATE EXTENSION pg_trgm;

This is normally packaged with PostgreSQL as a -contrib module. If this doesn't work for you check with your distro.

3

When using pg_trgm for trigram text matching you need

CREATE EXTENSION pg_grgm

As it is said in the official docs

I faced the same problem creating GIN indexes, but it's another problem. You also need some other extension:

CREATE EXTENSION btree_gin

Although it is not related to this topic, I found the solution here

1
  • 1
    You do not need the btree_gin extension to create this index. The btree_gin extension will do nothing here. Commented Jan 9, 2019 at 5:47
1

gin_trgm_ops is not a PostGIS feature. It's an available feature for the base database technology, PostgreSQL. It does have to be enabled by calling CREATE EXTENSION pg_trgm, but it's a text search feature, not a spatial feature. See the documentation for details. PostGIS only extends PostgreSQL with spatial types, functions, etc.

-1

Note that you also need to be connected to the database in which you want to create the pg_grgm extension.

e.g.

\c database_name
CREATE INDEX table_gin_trgm_idx ON table USING gin (col gin_trgm_ops);

Not the answer you're looking for? Browse other questions tagged or ask your own question.