PostgreSQL GIN pg_trgm default operator class

I am using GIN indexes with the pg_trgm module for indexing varchar fields. So, to define an index I have to write something like this:

CREATE INDEX gin_field_idx ON table_name USING gin (field gin_trgm_ops);

If I exclude the operator class (git_trgm_ops) and write:

CREATE INDEX gin_field_idx ON table_name USING gin (field);

PostgreSQL will raise an error:


ERROR: data type character varying has no default operator class for
access method "gin"

HINT: You must specify an operator class for the
index or define a default operator class for the data type.

How do I define a default operator method from the module?

This does not help. It describes how to define a new operator class from extension operators and functions. But I have to define the default operator class from a module.

Any help will be appreciated :-).

you can update the pg_opclass table and set the default.

update pg_opclass set opcdefault = true where opcname='gin_trgm_ops'

pg_trgm doesn't do this by default as you might have/want a different GIN default.

select * from pg_opclass where opcname = 'gin_trgm_ops';

than you can create an index:

create index ON table USING gin (field); -- it will use the new default

StackExchange Database Administrators Q#145586, answer score: 7