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.