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

by

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.

More Related Contents:

  1. What is PostgreSQL equivalent to autoincrement?
  2. How do I specify a password to ‘psql’ non-interactively?
  3. Use multiple conflict_target in ON CONFLICT clause
  4. PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function
  5. How to find table creation time?
  6. Go and IN clause in Postgres
  7. Where does PostgreSQL store the database?
  8. How to add “on delete cascade” constraints?
  9. Is there a way to disable function overloading in Postgres
  10. Grant all on a specific schema in the db to a group role in PostgreSQL
  11. Migrate database from Postgres to MySQL [closed]
  12. How can I start PostgreSQL on Windows?
  13. PostgreSQL – max number of parameters in “IN” clause?
  14. SQLAlchemy, Psycopg2 and Postgresql COPY
  15. How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?
  16. Why can I create a table with PRIMARY KEY on a nullable column?
  17. Oracle SQL Developer and PostgreSQL
  18. PostgreSQL array of elements that each are a foreign key
  19. How to reset sequence in postgres and fill id column with new data?
  20. Differentiate inserted and updated rows in UPSERT using system columns
  21. Postgres ERROR: could not open file for reading: Permission denied
  22. Custom ORDER BY Explanation
  23. Configure JPA to let PostgreSQL generate the primary key value
  24. ERROR: unterminated quoted string at or near
  25. How can I send some http request from postgresql function or trigger
  26. Alternate output format for psql showing one column per line with column name
  27. Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylib
  28. Destroying a Postgres DB on Heroku
  29. Loop through columns of RECORD
  30. How to drop PostgreSQL database through command line [closed]

Leave a Comment