What is gin_trgm_ops of PostgreSQL?

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?

Have you searched for the answer yourself? The very first hit from Google explained it to me. — Scro, Jun 24 '13 at 22:46
I'm not sure this is a good question for [gis.se] because it has nothing to do with PostGIS. This is a PostgreSQL question on Trigram indexing, and it's better suited for [dba.se] — Evan Carroll, Jan 09 '19 at 05:49

score 14 · Answer 1 · edited Jan 09 '19 at 05:49

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.

edited Jan 09 '19 at 05:49

Evan Carroll

7,071
2
32
58

answered Jun 24 '13 at 22:45

Devils Dream

3,179
9
44
78

score 3 · Answer 2 · edited Apr 13 '17 at 12:43

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

edited Apr 13 '17 at 12:43

Community

1

answered Jul 16 '13 at 09:56

user212473

39
1

1

You do not need the btree_gin extension to create this index. The btree_gin extension will do nothing here. – Evan Carroll Jan 09 '19 at 05:47

score 1 · Answer 3 · answered Dec 08 '16 at 22:11

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.

score -1 · Answer 4 · answered Mar 15 '18 at 23:27

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

answered Mar 15 '18 at 23:27

Ben Wilson

99
1

What is gin_trgm_ops of PostgreSQL?

4 Answers4