Learn more about “Type hstore does not exist” error. Our PostgreSQL Support team is here to help you with your questions and concerns.

Type hstore does not exist error | 4 Fixes

PostgreSQL’s hstore extension is a key-value pair data type. It allows flexible and dynamic storage of data within a single PostgreSQL column.

Also, it enables users to store sets of key-value pairs within a single database field. This makes it easier to manage semi-structured data.

However, it is likely to run into errors while dealing with PostgreSQL’s hstore extension within Rails.

Type hstore does not exist error

Our experts have put together some troubleshooting tips to help you out.

Method 1

For instance, one of our customers ran into trouble when they executed “CREATE EXTENSION hstore;”.

On closer examination, our experts noticed that CREATE EXTENSION installs the extension into the current database. Since it was executed under the superuser (postgres) in the main Postgres database, Rails and its application database did not know about its existence.

Fortunately, there is a way to avoid this error. All we have to do is install hstore in the template1 database, a template that PostgreSQL clones when creating new databases. This ensures that every new database inherits hstore by default:

psql template1 -c 'create extension hstore;'

Now, when the application databases are created, hstore will already be waiting for them. To set up existing databases with hstore, we can use psql as a superuser:

psql application_db -c 'create extension hstore;'

Method 2

Alternatively, we can opt to create a new schema called extensions and give authorization to the project’s database username:

psql -U postgres -d template1 -c "CREATE SCHEMA extensions AUTHORIZATION DbUserName;"

Then, create the extensions in the created schema.

psql -U postgres -d template1 -c "CREATE EXTENSION IF NOT EXISTS hstore SCHEMA extensions;"


If hstore extension is installed in a schema named hstore that is not on our default search_path, we have to do one of the following:

• During connection setup, add hstore to search_path.
• Or, add hstore to search_path with an ALTER DATABASE … SET or ALTER USER … SET.
• Alternatively, move the hstore extension from the hstore schema into public.
• Another option would be to schema-qualify the references to hstore. We need to do this for operators too.

Method 3

1. When all else fails, offer Postgres super user access.

   sudo su postgres
2. Then, run this command:

   psql -U postgres database_name -c 'create extension hstore;'
   Now, we can change the table database_name and add hstore type columns in it.
3. Next, connect to the database with this command:

   psql -d database_name -U user_role
alter table table_name add column_name HSTORE;

Let us know in the comments which one of the methods helped you fix the error.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

In brief, our Support Experts demonstrated how to fix the “Type hstore does not exist” error.