ERROR: type “hstore” does not exist

I was getting this error though I issued CREATE EXTENSION hstore;. When I used \dx+ in psql to see installed extensions, I found out that extensions are installed into schemas instead of being installed globally in the database. So the reason I got this error was that the schema in which I installed hstore was not on my search path. If you have a database with more than one schema and you want to use hstore extension, you can either put on search path the schema in which you installed hstore or you can reference hstore using schema in your SQL statements.

SET search_path = primary_schema, schema_with_hstore;

or

CREATE TABLE test (col1 schema_with_hstore.hstore);

Tags:

Leave a Reply

You must be logged in to post a comment.