PostgreSQL GIN pg_trgm default operator class

Question

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 :-).

• András Váczi: So you basically want to roll your own version of pg_trgm in order to save a few characters when creating indexes?
• CarefreeSlacker: @dezso No. It is necessary for greater good. I am adding pg_trgm to database of large Ruby on Rails(RoR) project. RoR have two ways of migrating database: 1 - by rake db:migrate or 2 - by loading schema.rb. In both cases it breaks older migrations indexes, those already has GIN index type, because they do not have described class. So i want to add default class for not to break older migrations.
• jjanes: If RoR doesn't do this migration properly, wouldn't it be better to fix RoR so that it does work properly, rather than changing pg_trgm in some customized way? Is your plan to make and maintain your own fork of pg_trgm?
• CMCDragonkai: I get this error even when I use gin("somecol", gin_trgm_ops). And I have the extension created too.

Answer 1

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

• András Váczi: Let me add a small advisory: manually changing the system catalog may result in unexpected behaviour. This solution works until there is no conflict on the default operator class: if another extension would have its set to default for type text, this change would cause a conflict.
• jjanes: This works immediately, but is pretty dreadful. You are almost guaranteeing yourself problems when it comes to upgrading your database or merging two databases together into one, or splitting one apart.
• cohenjo: Postgres doesn't have a preference to what is the default behavior, it's up to the DBA to make sure which opclass is default. As with any upgrade/migration the DBA will need to make sure that this setting persists.
• jjanes: @cohenjo Sure the DBA can micromanage the database, but isn't that directly at odds with what RoR is intended for?
• cohenjo: It's a simple update to run with the extension creation, it's a small price to pay :)