sseletskyy
2/16/2013 - 8:43 AM

I kept running into "PG::Error: ERROR: type "hstore" does not exist" because we had neglected to add hstore to our database and my migratio

I kept running into "PG::Error: ERROR: type "hstore" does not exist" because we had neglected to add hstore to our database and my migrations would constantly fail. Here are the steps to fix this problem:

Fixing missing hstore extension for postgres and rails.
content_copyfile_download
HSTORE PROBLEMS:

If the specs are not running because you are running into hstore issues:

1. In the postgresql console: 
update pg_database set datallowconn = TRUE where datname = 'template0';

This allows you to edit your postgresql default database template.

2. In your shell:
psql -d template0 -c 'create extension hstore;'

This sets the hstore extension as part of the default template.

3. In your shell
bundle exec rake db:drop

This removes your current development database

4. In your config/database.yml
Add this line to your development and test config

  template: template0
  
This tells rake to create your databasis with this template.

5. In your shell
bundle exec rake db:create
bundle exec rake db:migrate

6. In the postgresql console: 
update pg_database set datallowconn = FALSE where datname = 'template0';

This leaves the template with the permissions it originally had.

References:
Specifying template - http://stackoverflow.com/questions/5821238/rake-dbcreate-encoding-error-with-postgresql
Modifying the template to accept connections - https://gist.github.com/ffmike/877447
Shell command to add hstore extension to template - http://stackoverflow.com/questions/12540865/error-with-rails-test-database-using-postgres-using-hstore