When using hstore in Postgresql 9.2 in a Rails 3.2 app, I got an error complaining as follows when raking my test database:
PG::Error: ERROR: type "hstore" does not exist
Since it built from schema, the test database didn't go through the hstore CREATE EXTENSION migration that the development database. This caused the error on the rake db:test:prepare.
How to fix this? I've actually discovered a fix, happy to hear more.
I simply enabled my postgresql database to support hstore by default (by having the template database support hstore). Run the following command to do so:
psql -d template0 -c 'create extension hstore;'
Then any Rails test db will automatically support the extension.
psql -d template1 -c 'create extension hstore;' did the trick (Postgres 9.1). According to a post on PostgreSQL.org, "Template0 is the 'blank' pgsql database template that contains just those things that come out-of-the-box."When I tried to run psql -d template0 -c 'create extension hstore;' (in @Connor's answer) I got the error:
psql: FATAL: database "template0" is not currently accepting connections
Instead I followed the procedure in this blog post which involves updating template1 instead.
1) Create file “hstore.sql” containing:
CREATE EXTENSION hstore;
2) Run it:
psql -f /usr/local/Cellar/postgresql/9.2.1/share/postgresql/extension/hstore.sql -d template1
I suspect that this would also have worked (but I didn't try it):
psql -d template1 -c 'create extension hstore;'
(To see the different write permissions between template0 and template1 I followed this article)
To solve the FATAL error and allow template0 to accept connections, execute the following:
UPDATE pg_database SET datallowconn = TRUE WHERE datname = 'template0';
pg_upgrade to fail later - be careful when the time comes to upgrade.For Heroku
heroku pg:psql --app YOUR_APP_NAME
and then run
CREATE EXTENSION hstore;
