找到你要的答案

Q:PG::UndefinedObject: ERROR: type “hstore” does not exist but it does

Q:PG::undefinedobject:错误:类型“汇存”并不存在,但它确实

First of all, this may look like a duplicate of:

postgres hstore exists and doesn't exist at same time

but it is not. While I am getting the same error message in the circumstance. When checking to see if hstore is installed on the DB, we can see that it is:

./psql -d photographerio_development -c '\dx'
                       List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 hstore  | 1.2     | hstore     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

and it is too on the template_1 DB.

So, when I try to run the migration to add the hstore, I get the PG::Error: ERROR: extension "hstore" already exists and when I comment out this migration, on the next one, which requires the hstore, it says PG::UndefinedObject: ERROR: type "hstore" does not exist which is a bit of a paradox.

It is a Rails 4.0.1 app with postgresql 9 and I have hstore working on a few other projects running on this machine.

首先,这可能看起来像一个复制品:

Postgres汇存存在不同时存在

但它不是。虽然我得到相同的错误信息的情况下。当检查汇存上安装的数据库,我们可以看到它:

./psql -d photographerio_development -c '\dx'
                       List of installed extensions
  Name   | Version |   Schema   |                   Description                    
---------+---------+------------+--------------------------------------------------
 hstore  | 1.2     | hstore     | data type for storing sets of (key, value) pairs
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

也是在template_1分贝。

所以,当我试图运行迁移加汇存,我的PG::错误:错误:推广“汇存”已经存在,当我评论了这种迁移,在下一个,这就要求汇存,它说:PG:undefinedobject:错误:类型“汇存”不存在这是一个悖论。

这是一个Rails 4.0.1应用PostgreSQL 9我汇存工作的其他一些项目在本机上运行。

answer1: 回答1:

You have installed the hstore extension in a schema named hstore which is presumably not on your default search_path.

You must do one of these:

  • Add hstore to search_path during connection setup;
  • Add hstore to search_path with an ALTER USER ... SET or ALTER DATABASE ... SET;
  • Move the hstore extension from the hstore schema into public; or
  • schema-qualify all references to hstore, e.g. hstore.hstore(...). This must be done for operators too; -> becomes OPERATOR(hstore.->)

你已经安装在架构命名为汇存这大概是不是默认的扩展search_path汇存。

你必须做其中之一:

  • Add hstore to search_path during connection setup;
  • Add hstore to search_path with an ALTER USER ... SET or ALTER DATABASE ... SET;
  • Move the hstore extension from the hstore schema into public; or
  • schema-qualify all references to hstore, e.g. hstore.hstore(...). This must be done for operators too; -> becomes OPERATOR(hstore.->)
answer2: 回答2:

This is how I solved the problem. Create a schema called extensions and grant authorization to the db username you use in the project.

psql -U postgres -d template1 -c "CREATE SCHEMA extensions AUTHORIZATION <yourDbUserName>;"

create the extensions in the created schema (extensions)

psql -U postgres -d template1 -c "CREATE EXTENSION IF NOT EXISTS hstore SCHEMA extensions;"

This is how I solved the problem. Create a schema called extensions and grant authorization to the db username you use in the project.

psql -U postgres -d template1 -c "CREATE SCHEMA extensions AUTHORIZATION <yourDbUserName>;"

在所创建的架构(扩展)中创建扩展名

psql -U postgres -d template1 -c "CREATE EXTENSION IF NOT EXISTS hstore SCHEMA extensions;"
ruby-on-rails  postgresql  hstore