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:
Contact Us