How to create a new database with the hstore extension already installed?

by

Long story short:

Install hstore in the template1 database:

psql -d template1 -c 'create extension hstore;'

Step-by-step explanation:

As stated by the PostgreSQL documentation:

CREATE EXTENSION loads a new extension into the current database.

Installing an extension is database-specific. The following returns you the current database name:

$ psql -c 'select current_database()'
 current_database 
------------------
 username
(1 row)

In case you have a database named after your username. Now with dbtest:

$ psql -d dbtest -c 'select current_database()'
 current_database 
------------------
 dbtest
(1 row)

Ok, you got it. Now, to create new databases with hstore installed, you’ll have to install it in the template1 database. According to the doc:

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1.

Let’s do this:

$ psql -d template1 -c 'create extension hstore;'

And check that it works :

$ createdb dbtest
$ psql -d dbtest -c '\dx'
                 List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 hstore  | 1.0     | public     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

Done!

More Related Contents:

  1. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  2. PL/pgSQL checking if a row exists
  3. Compute percents from SUM() in the same SELECT sql query
  4. Postgres analogue to CROSS APPLY in SQL Server
  5. How to find a table having a specific column in postgresql
  6. Postgres function returning table not returning data in columns
  7. Check if NULL exists in Postgres array
  8. How to use SQL LIKE condition with multiple values in PostgreSQL?
  9. How to perform a select query in a DO block?
  10. Join a count query on generate_series() and retrieve Null values as ‘0’
  11. PostgreSQL – dynamic value as table name [duplicate]
  12. must appear in the GROUP BY clause or be used in an aggregate function
  13. Grouped LIMIT in PostgreSQL: show the first N rows for each group?
  14. Possible to perform cross-database queries with PostgreSQL?
  15. Optimize query with OFFSET on large table
  16. GROUP BY and aggregate sequential numeric values
  17. Safely rename tables using serial primary key columns
  18. How to concatenate columns in a Postgres SELECT?
  19. How to use (install) dblink in PostgreSQL?
  20. PostgreSQL multi INSERT…RETURNING with multiple columns
  21. Postgres manually alter sequence
  22. What’s the proper index for querying structures in arrays in Postgres jsonb?
  23. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  24. Oracle equivalent of Postgres’ DISTINCT ON?
  25. postgresql generate sequence with no gap
  26. Combine two columns and add into one new column
  27. Bulk/batch update/upsert in PostgreSQL
  28. Grant privileges for a particular database in PostgreSQL
  29. Functions with variable number of input parameters
  30. Timestamp Difference In Hours for PostgreSQL

Leave a Comment