Introduction to hstore

The PostgreSQL extension, hstore, implements a data type for storing sets of key/value pairs within a single PostgreSQL value. This can be useful in various scenarios, such as rows with many attributes that are rarely examined or semi-structured data. Keys and values are simply text strings.

We could go so far as to say that hstore is a precursor to JSON support in PostgreSQL, as it supports some of the same use cases. The main difference between hstore and JSON is that in hstore there’s only one data type supported, and that’s text. Also, an hstore composite value is a flat dictionary, so nesting isn’t supported.

Still, hstore is very useful in some cases, and we’re going to see how to put hstore into practice to audit changes in a generic way.

How to enable hstore?

Of course, the first thing we have to do is to enable the hstore extension in our database with the following SQL command: