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.
Best Answer
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 operatorTo fix it run
SET search_path TO "$user", public,
followed by the schema where the extension is installed.Hope that helps!