Best way to install hstore on multiple schemas in a Postgres database? Best way to install hstore on multiple schemas in a Postgres database? postgresql postgresql

Best way to install hstore on multiple schemas in a Postgres database?


It is not allowed to install extensions multiple times per database. Quoting the manual on CREATE EXTENSION:

Remember that the extension itself is not considered to be within any schema: extensions have unqualified names that must be unique database-wide. But objects belonging to the extension can be within schemas.

If you don't want to include public in your search_path, install "public" extensions into a dedicated schema (example: extensions). I would use a single schema for all of them, not a separate schema for each extension. There are quite a few of them.CREATE EXTENSION offers an option to install to an existing schema of your choice:

    CREATE EXTENSION hstore SCHEMA extensions;

And make sure the schema is included in the search_path of users who might want to make use of it.

Data storage is not affected at all by the schema the extension resides in.


Try this to install extension hstore on all schemas

create extension hstore schema pg_catalog;