What is gin_trgm_ops of PostgreSQL? [closed]

Question

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?

Answer 1

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.

Answer 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

Answer 3

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.

Answer 4

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);