4

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?

closed as off-topic by Evan Carroll, Jochen Schwarze, BERA, Mark Ireland, xunilk Jan 9 at 20:24

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions relating to general IT or with no clear GIS component, are off-topic here but can be researched/asked at Stack Overflow (software development), Super User (computing hardware and software), Database Administrators (relational databases) and other SE sites" – Evan Carroll, Jochen Schwarze, BERA, Mark Ireland, xunilk
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 6
    Have you searched for the answer yourself? The very first hit from Google explained it to me. – Scro Jun 24 '13 at 22:46
  • 1
    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 – Evan Carroll Jan 9 at 5:49
10

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.

2

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

  • You do not need the btree_gin extension to create this index. The btree_gin extension will do nothing here. – Evan Carroll Jan 9 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.