Postgresq error: type 'hstore' does not exist
5 Aug, 2015
1 minute read

Today I was programming and messed up my database schema in such a way that I just wanted to restore a recent backup and start over from there.

dropdb app_development
createdb app_development

Unfortunately I ran into an error:

PostgreSQL error: type ‘hstore’ does not exist

hstore is a Postgresql extension (available since postgresql-9.x) that allows you store sets of key/value pairs within a single Postgresql value (read column). Read more on hstore in the Postgresql documentation.

Anyway, the hstore extension is not loaded for my brand new database. No problem, simply add it with this command:

psql app_development -c 'create extension hstore;'

That’s all.

Bonus points

If you use hstore for almost any database, you could add it to the template1 database.

FYI: Postgresql creates a new databases by copying another. template1 is a default database provided by Postgresql for the purpose of creating new database.

Adding hstore to template1 is just as easy as adding it to your own database.

psql template1 -c 'create extension hstore;'

Whenever your use createdb now, the new database will have the hstore extension by default.

If you're seeing this message you've probably enabled some form of adblocker. I respect that.

I use this space to show recommended books on Amazon.com in relation to the post you just read. This generates a small income that allows me to buy a book every now and then.