Create HSTORE with multiple schemas

Question

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.

Answer 1

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;