0

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?

  • 1
    "I tried namespacing the operator class (myschema.gin_trgm_ops) to no avail" That works for me. Please show the entire command you tried to execute and the error you got. – jjanes Aug 29 at 0:43
  • @jjanes I've updated the question with the detail. – Esteban Aug 29 at 4:24
  • 1
    That still works for me. What version of PostgreSQL are using? Was this the first error, or was there some earlier error (for example, did the CREATE EXTENSION...WITH SCHEMA actually succeed?) – jjanes Aug 29 at 13:47
1

You can adjust the search_path before running the DDL statements:

set search_path = myschema,public;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.