hstore type does not exist with hstore installed postgresql

Question

I recently had to copy over a databases structure from one database to another with the command

pg_dump -c -S database_name > pg_dump_date.sql

I then ran the dump file on the new database with

psql < pg_dump_date.sql

And my schemas were recreated, as well as my extensions were installed.

After checking the installed extensions I can see that hstore is installed, but when I try basic operators for hstore like

SELECT id FROM schema_name.table_name WHERE hstore_column->'hstore_key'::TEXT = 'hstore_value'

I get the error that

operator does not exist: schema_name.hstore -> text 

I get a similar error if I do not try and cast the key

operator does not exist: schema_name.hstore -> unkown

My first instinct was to simply drop the hstore extension and re-install it, but doing so would also drop my numerous user functions, triggers, and other dependencies on the hstore type.

Short of doing a cascading drop of the hstore exentsion, is there a way that I can fix this error? I am using a postgresql 9.3 server.

Answer 1

For anyone that runs into this question. I ran into this problem today and found a solution.

Depending on where you have the extension installed, it may not be in your search_path. In my case, i had it installed into the schema 'shared_extensions' on the same db.

using SHOW search_path displayed $user, public, which means that it was never looking in shared_extensions for the extension and it's operator

To fix it run SET search_path TO "$user", public, followed by the schema where the extension is installed.

Hope that helps!

Answer 2

I just fixed it. I realized that I created the extension within the specific schema scope. So I deleted the extension from there and created within public schema.

Hope it helps.