How to create a new database with the hstore extension already installed?
hstore, postgresql, postgresql-9.1, sql
Solution
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!
Problem
Recently I went into trouble trying to use hstore with Django. I installed hstore this way: ``` $ sudo -u postgres psql postgres=# CREATE EXTENSION hstore; WARNING: => is deprecated as an operator name DETAIL: This name may be disallowed altogether in future versions of PostgreSQL. CREATE EXTENSION postgres=# \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) ``` And naively thought that my new databases would include hstore. That ain't the case: ``` $ createdb dbtest $ psql -d dbtest -c '\dx' List of installed extensions Name | Version | Schema | Description ---------+---------+------------+------------------------------ plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (1 row) ``` Is there a way to automatically have hstore in a newly created database ?