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 ?
The hstore module is used to implement the hstore data type in the form of key-value pairs for a single value within PostgreSQL. The hstore data type is remarkably effective in many cases, such as, multiple rows with multiple attributes which are rarely queried for or semi-structured data.
hstore is a PostgreSQL extension that implements the hstore data type. It's a key-value data type for PostgreSQL that's been around since before JSON and JSONB data types were added.
Description. CREATE EXTENSION loads a new extension into the current database. There must not be an extension of the same name already loaded. Loading an extension essentially amounts to running the extension's script file.
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!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With