Create GIN index in a schema with the pg_trgm extension

Question

I'm migrating entities from the public schema into a new one, let's call it myschema. Originally, the pg_trgm extension was installed and the index was created in a schema migration with:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX mytable_gin_index_on_mycolumn
    ON mytable USING gin (mycolumn gin_trgm_ops);

To do the same within myschema I modified the migration to look like this:

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA myschema;
CREATE INDEX mytable_gin_index_on_mycolumn
    ON mytable USING gin (mycolumn gin_trgm_ops);

However, index creation fails with the following error:

operator class "gin_trgm_ops" does not exist for access method "gin"

I tried namespacing the operator class:

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA myschema;
CREATE INDEX mytable_gin_index_on_mycolumn
    ON mytable USING gin (mycolumn myschema.gin_trgm_ops);

but got the same error:

operator class "myschema.gin_trgm_ops" does not exist for access method "gin"

I also tried to install the extension without specifying the schema, which yields the same error.

What is the right way to use the pg_trgm extension in a schema other than public?

Answer 1

You can adjust the search_path before running the DDL statements:

set search_path = myschema,public;