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.