CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4hstore
Boris Barroso
Boris Barroso

Reputation: 1812

Create HSTORE with multiple schemas

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.

Upvotes: 3

Views: 1706

Answers (3)

Craig Ringer
Craig Ringer

Reputation: 325051

If you're using multiple different schemas, I suggest putting hstore in its own and ensuring it is always on the search_path. You might not want public on your search_path at all times, and it's nice to keep things compartmentalized.

CREATE SCHEMA hstore;
CREATE EXTENSION hstore WITH SCHEMA hstore;

... then either amend your search_path consistently, or just always schema-qualify everything, using hstore.hstore as the type name, OPERATOR(hstore.->). E.g.

SELECT hstore.hstore('"x" => "42"') OPERATOR(hstore.->) "x"

Alternately, it's safe to install hstore into pg_catalog:

CREATE EXTENSION hstore WITH SCHEMA pg_catalog;

pg_catalog is always implicitly on the search path.

Upvotes: 5

Pierre
Pierre

Reputation: 8348

the alternative to Peter Eisentraut's answer is to amend your search path so that the public schema is always on the search path.

This is particularly useful if you rely on schemas for a multitenant app (which is my case).

In your database.yml file you would put the following instruction:

schema_search_path: "schema2, public"

note: put your main schema first.

if you want to change the search path in a more dynamic way in your code, you can play with connection.schema_search_path

Upvotes: 1

Peter Eisentraut
Peter Eisentraut

Reputation: 36759

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;

Upvotes: 5

Related Questions