A hash store, also known as key-value store, or associative array is a famous data structure in modern programming languages such as Java, Python, and Node.js. Also, there are dedicated database frameworks to handle this kind of data, such as the Redis database.

PostgreSQL has supported hash store—hstore—since PostgreSQL version 9.0. The hstore extension allows developers to leverage the best of both worlds. It increases the developer's agility without sacrificing the powerful features of PostgreSQL. Also, hstore allows the developer to model semistructured data and sparse arrays in a relational model.

To create the hstore, one simply needs to execute the following command as a superuser:

CREATE EXTENSION hstore;

The textual representation of hstore includes a zero or higher key=> value pair followed by a comma. An example of the hstore data type is as follows:

SELECT 'tires=>"winter tires", seat=>leather'::hstore;
                   hstore 
--------------------------------------------
 "seat"=>"leather", "tires"=>"winter tires"
(1 row)

One could also generate a single value hstore using the hstore(key, value) function as shown below:

SELECT hstore('´Hello', 'World');
      hstore 
-------------------
 "´Hello"=>"World"
(1 row)

Note that, in hstore, keys are unique, as shown in the following example:

SELECT 'a=>1, a=>2'::hstore;
  hstore 
----------
 "a"=>"1"
(1 row)

In the car web portal, let's assume that the developer wants to support several other attributes, such as airbags, air conditioning, power steering, and so on. The developer, in the traditional relational model, should alter the table structure and add new columns. Thanks to hstore, the developer can store this information using the key-value store without having to keep altering the table structure, as follows:

car_portal=# ALTER TABLE car_portal_app.car ADD COLUMN features hstore;
ALTER TABLE

One limitation of the hstore is that it is not a full document store, so it is difficult to represent nested objects in an hstore. The other problem is maintaining the set of keys, since a hstore key is case sensitive. 

car_portal=# SELECT 'color=>red, Color=>blue'::hstore;
             hstore 
---------------------------------
 "Color"=>"blue", "color"=>"red"
(1 row)

The -> operator is used to get a value for a certain key. To append an hstore, the || concatenation operator can be used. Furthermore, the minus sign - is used to delete a key-value pair. To update an hstore, the hstore can be concatenated with another hstore that contains the updated value. The following example shows how hstore keys can be inserted, updated, and deleted:

CREATE TABLE features (
        features hstore
);

The following example demonstrates insert, update and delete operations:

car_portal=# INSERT INTO features (features) VALUES ('Engine=>Diesel'::hstore) RETURNING *;
      features 
--------------------
 "Engine"=>"Diesel"
(1 row)

INSERT 0 1
car_portal=# -- To add a new key
car_portal=# UPDATE features SET features = features || hstore ('Seat', 'Lethear') RETURNING *;
               features 
---------------------------------------
 "Seat"=>"Lethear", "Engine"=>"Diesel"
(1 row)

UPDATE 1
car_portal=# -- To update a key, this is similar to add a key
car_portal=# UPDATE features SET features = features || hstore ('Engine', 'Petrol') RETURNING *;
               features 
---------------------------------------
 "Seat"=>"Lethear", "Engine"=>"Petrol"
(1 row)

UPDATE 1
car_portal=# -- To delete a key
car_portal=# UPDATE features SET features = features - 'Seat'::TEXT RETURNING *;
      features 
--------------------
 "Engine"=>"Petrol"
(1 row)

As an hstore data type can be converted to a set using each (hstore) function, this allows the developer to use all the relational algebra set operators on an hstore, such as DISTINCT, GROUP BY, and ORDER BY.

The following example shows how to get distinct hstore keys; this could be used to validate hstore keys:

car_portal=# SELECT DISTINCT (each(features)).key FROM features;
  key 
--------
 Engine
(1 row)

To get the hstore as a set, one can simply use each function again:

car_portal=# SELECT (each(features)).* FROM features;
 key | value 
--------+--------
 Engine | Petrol
(1 row)