Create HSTORE with multiple schemas

hstore, postgresql, ruby-on-rails, ruby-on-rails-4

Solution

You need to refer to your objects in a way that is consistent with your schema naming and search path. For example:

CREATE EXTENSION hstore SCHEMA public;

SET search_path TO schema2;
ALTER TABLE accounts ADD COLUMN extras public.hstore;

or

SET search_path TO public;
ALTER TABLE schema2.accounts ADD COLUMN extras hstore;

Problem

I have been trying to migrate my database to have HSTORE but the extension only works for public SCHEMA when I want to add an HSTORE column in other schemas it does not work ``` def up # My hstore looks like this execute "CREATE EXTENSION hstore SCHEMA public" # I have also tried # execute "CREATE EXTENSION hstore" end ``` but when I run my next migration it just doesn't work and if I go to psql console and alter tables I get this: ``` set search_path to public; alter table accounts add column extras hstore; -- Works fine set search_path to schema2; alter table accounts add column extras hstore; -- Raises an error ``` I'm using rails 4 Thanks.

Original source