brite, a MVC Framework for jQuery

Postgres: create extension pg_trgm (and other extensions) for multiple schema

November 25th, 2013 by jeremychone

Create extension for multiple schema

When, creating any extensions, make sure you do the with schema pg_catalog to have those extensions available for all schemas of the database.

For example, to create the pg_trgm index extension, do as follow once connected to your database. Not having the with schema pg_catalog will lead to the typical ERROR: operator class “gin_trgm_ops” does not exist for access method “gin” on your second schema if you have a multiple schema setup.

mydb=# create extension pg_trgm with schema pg_catalog;

To list installed extensions:

mydb=# dx

To alter already installed extension

mydb=# ALTER EXTENSION pg_trgm SET SCHEMA pg_catalog;

Postgres Alter Extension Doc

Comments are closed.