4

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.

6

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.