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

Question

I recently ran into a problem 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 believed that my new databases would include hstore. This is not true:

 $ 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?

Answer 1

In short:

Install hstore in the template1 database:

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

---

Step by step explanation:

As indicated by the PostgreSQL documentation :

CREATE EXTENSION loads the new extension into the current database.

The installation of the extension depends on the specific database. The following returns the name of the current database:

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

If you have a database with your username. Now with dbtest :

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

Ok, you get it. Now to create new databases with hstore installed, you will need to install it in the template1 database. According to the document :

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

Do it:

 $ 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!