使用多个架构创建HSTORE [英] Create HSTORE with multiple schemas

问题描述

我一直在尝试将数据库迁移为具有HSTORE，但是当我想在其他模式中添加HSTORE列不起作用时，该扩展名仅适用于 public SCHEMA

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

但是当我运行时我的下一次迁移不起作用，如果我转到psql控制台并更改表，则会得到以下信息：

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

我正在使用rails 4
谢谢。

I'm using rails 4 Thanks.

推荐答案

您需要以与架构命名和搜索路径一致的方式引用对象。例如：

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;

或

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

这篇关于使用多个架构创建HSTORE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！