How to Create a New Database with the Hstore Extension Already Installed

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

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!

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

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.

• How does the search_path influence identifier resolution and the "current schema"

Data storage is not affected at all by the schema the extension resides in.

PostgreSql hstore extension with public prefix issue

You can check if and where the extension is already installed using the command \dx hstore

postgres hstore exists and doesn't exist at same time

To create extension in your database, you have to explicitly connect to that database. So, if your database is my_app_development, you have to do :

sudo -u postgres psql my_app_development
create extension hstore;

Also, you do not tell which rails version you're on. If you're not on rails-4, you will have to use the postgres hstore gem.

How do I install the hstore module on PostgreSQL 9.0 (MacPorts install)?

You can tell MacPorts to build hstore. Here's how.

If you already have postgresql installed, you will need to uninstall it first (this won't touch your data or users) because the install action will not re-install an already installed port. The uninstall is forced (-f) because postgresql91-server is dependent and will prevent uninstall.

sudo port -f uninstall postgresql91

Edit the Portfile and add hstore to the list on the line which begins with set contribs:

sudo port edit postgresql91

(Re)install from source explicitly (-s) to build the hstore extension:

sudo port -s install postgresql91

Then load hstore, once for each of your databases in which you want to use it:

In >= 9.1: CREATE EXTENSION hstore;

In 9.0: psql -U postgres -f /opt/local/share/postgresql90/contrib/hstore.sql

Note this process works for postgresql92 by just substituting "92" for "91".

How to setup django-hstore with an existing app managed by south?

I eventually found that the hstore extension wasn't installed for the specific database I was using:

$ psql -d mydb
psql (9.1.4)
Type "help" for help.

mydb=# SELECT t.oid, typarray FROM pg_type t JOIN pg_namespace ns ON typnamespace = ns.oid WHERE typname = 'hstore';
 oid | typarray 
-----+----------
(0 rows)

mydb=# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

mydb=# create extension hstore;
WARNING:  => is deprecated as an operator name
DETAIL:  This name may be disallowed altogether in future versions of PostgreSQL.
CREATE EXTENSION
mydb=# \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)

mydb=# SELECT t.oid, typarray FROM pg_type t JOIN pg_namespace ns ON typnamespace = ns.oid WHERE typname = 'hstore';
  oid  | typarray 
-------+----------
 58800 |    58805
(1 row)

I thought that a database created after the hstore installation would include the extension. Doesn't seem to be the case, am I misinterpreting how extensions work ? Are they database-specific ?

---

Related Topics