postgres hstore exists and doesn't exist at same time

Question

I set up a Rails app on a remote server and created an hstore extension

 sudo -u postgres psql
 CREATE EXTENSION hstore;

I then deployed an iteration of the app that uses hstore in one of the postgres tables, but when it ran the migrations it gave an error message

PG::UndefinedObject: ERROR:  type "hstore" does not exist

I then tried to do this again

 sudo -u postgres psql
 CREATE EXTENSION hstore;

but it told me hstore already exists

ERROR:  extension "hstore" already exists

and this circle continued on.

Any idea what might be causing this problem? I'm using postgres 9.1 on an Ubuntu 12.04 server

Update Note, wondering if this issue was related to permissions, I tried to check my permissions like this but got the following error

sudo -u postgres psql -U username
psql: FATAL:  Peer authentication failed for user "username"

Update Although hstore is installed, it's not an extension for the database I'm using. How to install it in a specific database?

psql -d db_production -c '\dx'
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

This is not a duplicate question. It just needs a better title such as, "Type 'hstore' does not exist for ActiveRecord Table" Simply add "execute 'create extension hstore'" just above the create_table command in the ActiveRecord::Migration and redo the migration. No need to open a separate psql session. This is yet another example of why Rails is so awesome. Easy Peasy. — l3x, Feb 04 '14 at 18:00

score 33 · Accepted Answer · answered Oct 19 '13 at 15:40

33

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.

answered Oct 19 '13 at 15:40

kik

7,867
2
31
32

2

I have tried your steps but still its saying `ERROR: extension "hstore" already exists`. – kamal Apr 22 '16 at 05:02

score 7 · Answer 2 · edited May 23 '17 at 11:46

7

Using this SO answer How to create a new database with the hstore extension already installed?, I found out that I had to create the extension for the template1 database and then any other database I created thereafter would have it installed

edited May 23 '17 at 11:46

Community

1
1

answered Oct 19 '13 at 15:39

Tomoko Yamaguchi

487
1
6
21

No—see @I3x's comment. That should work fine. – iconoclast Jun 23 '15 at 21:34

postgres hstore exists and doesn't exist at same time

2 Answers2

Linked