PostgreSQL has an extension module called
hstore that allows you to store semi-structured data in a key/value format. Values of an
hstore object are stored like in a dictionary. You can also reference its values in SQL queries.
To use the extension, it must first be loaded into the current database:
CREATE EXTENSION hstore;
Now you can use the data type
hstore. Here, we create a table with some regular columns and one column of type
CREATE TABLE animals ( id serial PRIMARY KEY, name text, props hstore );
Literals of type
hstore are written in single quotes, containing a set of
key => value pairs separated by commas:
INSERT INTO animals (name, props) VALUES ('Octopus', 'arms => 8, habitat => sea, color => varying'), ('Cat', 'legs => 4, fur => soft'), ('Bee', 'legs => 6, wings => 4, likes => pollen');
The order of the pairs is irrelevant. Keys within a hstore are unique. If you declare the same key more than once only one instance will be kept and the others will be discarded. You can use double quotes to include spaces or special characters:
'"fun-fact" => "Cats sleep for around 13 to 16 hours a day (70% of their life)"'
If the type of the literal can’t be inferred you can append
::hstore as a type indicator:
'legs => 4, fur => soft'::hstore
Both keys and values are stored as strings, so these two are equivalent:
'legs => 4, fur => soft' '"legs" => "4", "fur" => "soft"'
Another limitation of
hstore values is that they cannot be nested, which means they are less powerful than JSON objects.
You can use the
-> operator to dereference a key, for example in a SELECT:
SELECT name, props->'legs' AS number_of_legs FROM animals;
NULL if the key is not present. Of course, you can also use it in a WHERE clause:
SELECT * FROM animals WHERE props->'fur' = 'soft';
There are many other operators and functions that can be used with hstore objects. Here is a small selection (please refer to the documentation for a complete list):
||operator concatenates (merges) two hstores:
a || b
?operator checks the existence of a key and returns a boolean value:
props ? 'fur'
-operator deletes a key from a hstore:
props - 'fur'
akeysfunction returns an array of a hstore’s keys:
You can also convert a hstore object to JSON:
hstore_to_json(hstore). If you want to learn more about JSON in PostgreSQL you can continue reading this blog post: Working with JSON data in PostgreSQL