PostgreSQL – hstore Data Type

The hstore module is used to implement the hstore data type in the form of key-value pairs for a single value within PostgreSQL. The hstore data type is remarkably effective in many cases, such as, multiple rows with multiple attributes which are rarely queried for or semi-structured data.

Syntax: variable_name hstore;

It’s pretty simple to enable the hstore extension for using the hstore data type using the below command:

CREATE EXTENSION hstore;

Now let’s look into a few examples for better understanding.
Example 1:
First we create a books table with id as the primary key that identifies the book, the title as the title of the products and attr that stores attributes of the book such as ISBN, weight, and paperback. The data type of the attr column is the hstore using the below command:

CREATE TABLE books (
    id serial primary key,
    title VARCHAR (255),
    attr hstore
);

Now we add some data to our books table using the below command:

INSERT INTO books (title, attr)
VALUES
    (
        'Winds Of Winter',
        '"paperback" => "2403",
       "publisher" => "Bantam Spectra/US & Voyager Books/UK",
       "language"  => "English",
       "ISBN-13"   => "978-1449370000",
         "weight"    => "13.2 ounces"'
    ),
       (
        'A Dance with Dragons',
        '"paperback" => "2553",
       "publisher" => "Bantam Spectra/US & Voyager Books/UK",
       "language"  => "English",
       "ISBN-13"   => "978-1449370001",
         "weight"    => "14.2 ounces"'
    ),
       (
        'A Dream of Spring',
        '"paperback" => "2683",
       "publisher" => "Bantam Spectra/US & Voyager Books/UK",
       "language"  => "English",
       "ISBN-13"   => "978-1449370002",
         "weight"    => "15.7 ounces"'
    );

Now we query for the same inserted data using the below command:

SELECT
    attr
FROM
    books;

Output:

Example 2:
Postgresql hstore supports the use of -> operator. This is used to query the value of a particular key from an hstore column. For instance, if we want to query for the ISBN-13 of all available books in the books table, the same operator can be used as shown below:

SELECT
    attr -> 'ISBN-13' AS isbn
FROM
    books;

Output: