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

Question

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 ?

Hammons

Answer 1

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!

Hammons

+1 for being correct and putting it all in a useful format. One might consider to use a different database than template1. Any database can serve as template: CREATE DATABASE foo TEMPLATE mytemplate. Or, once you have additional stuff in template1, you can use the (empty by default) template0. – Terefah

Answer 2

Also remember, that hstore is in the Public schema, so if you use other schemas, you should use format public.hstore(record)

Torras