Cannot add hstore column to multitenant rails 4 applicaiton (postgres schema based)

Question

I'm trying to add hstore to my application. However i get an error telling me that hstore does not exists.

PG::UndefinedObject: ERROR:  type "hstore" does not exist
LINE 1: ALTER TABLE "people" ADD COLUMN "custom_fields" hstore

This is my migration:

class AddCustomFieldsToPeople < ActiveRecord::Migration
  def up
    add_column :people, :custom_fields, :hstore
  end

  def down
    remove_column :people, :custom_fields
  end
end

Acording to this SO answer i do not have to create the hstore extention on every schema but only add it to for example the public schema.

Is there anything i need to do other that adding the postgres extension to get hstore to work?

Any help is appreciated. :)

I have already added hstore. so i get ERROR: 'extension "hstore" already exists' :-/ — Ole Henrik Skogstrøm, Jun 06 '14 at 20:16
There is no response. imagesite_development=# SELECT 'a=>1'::hstore imagesite_development-# — Ole Henrik Skogstrøm, Jun 07 '14 at 07:33
Hmm, try this: drop schema public cascade; create schema public; CREATE EXTENSION IF NOT EXISTS hstore; And again: SELECT 'a=>1'::hstore; — Vakiliy, Jun 07 '14 at 08:04
I didn't want to resett my development database, so i tried running 'DROP EXTENSION IF EXISTS hstore;' and then add it again with 'CREATE EXTENSION IF NOT EXISTS hstore;' i then got this working all inside psql. however Rails is still not allowing me to add hstore columns using migrations. — Ole Henrik Skogstrøm, Jun 07 '14 at 08:39
I understand that you do not have migration works, but first want to make sure that the extension works in the database itself. For example in my DB: SELECT 'a=>1'::hstore; Result: hstore ---------- "a"=>"1" (1 row) — Vakiliy, Jun 07 '14 at 08:48
Yes. this works in my database: DB: SELECT 'a=>1'::hstore; Result: hstore ---------- "a"=>"1" — Ole Henrik Skogstrøm, Jun 07 '14 at 08:52
Try then now: ALTER DATABASE SET search_path = public, hstore; and run migration again. — Vakiliy, Jun 07 '14 at 09:26
well, let's try the other side, in migration insert: enable_extension "hstore" above add_column, run migration, and show results :) — Vakiliy, Jun 07 '14 at 09:36
Still the same error :-/, can't understand what I'm doing wrong. — Ole Henrik Skogstrøm, Jun 07 '14 at 09:40
No, i followed this railscast episode when i created the multitenancy: http://railscasts.com/episodes/389-multitenancy-with-postgresql — Ole Henrik Skogstrøm, Jul 12 '14 at 08:50
If you are using any Configuration management system like Chef or Puppet, add hstore to template1 database in PostgreSQL. That avoids from having a migration to do this. — MIdhun Krishna, Sep 23 '14 at 05:00

score 1 · Answer 1 · answered Jan 07 '15 at 05:55

Generate a migration with this and rake:

class AddHstore < ActiveRecord::Migration  
  def up
    enable_extension :hstore
  end

  def down
    disable_extension :hstore
  end
end

Since you will have a migration pending already, use rake db:migrate:up VERSION=

Cannot add hstore column to multitenant rails 4 applicaiton (postgres schema based)

1 Answers1