PG::UndefinedObject: ERROR: type "hstore" does not exist but it does

Alain picture Alain · Feb 20, 2014 · Viewed 11.7k times · Source

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.

Answer

Craig Ringer picture Craig Ringer · Feb 20, 2014

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.->)