August 2, 2023
Summary: in this tutorial, you’ll learn how to work with PostgreSQL hstore data type.
The hstore module implements the hstore data type for storing key-value pairs in a single value.
The hstore data type is very useful in many cases, such as semi-structured data or rows with many attributes that are rarely queried. Notice that keys and values are just text strings only.
Enable PostgreSQL hstore extension
Before working with the hstore data type, you need to enable the hstore extension which loads the contrib module to your PostgreSQL instance.
CREATE EXTENSION hstore;
Create a table with hstore data type
We create a table named
books that has three columns:
idis the primary key that identifies the book.
titleis 0the title of the products
attrstores attributes of the book such as ISBN, weight, and paperback. The data type of the attr column is hstore.
We use the CREATE TABLE statement to create the
books table as follows:
CREATE TABLE books ( id serial primary key, title VARCHAR (255), attr hstore );
Insert data into PostgreSQL hstore column
We use the
INSERT statement to insert data into the hstore column as follows:
INSERT INTO books (title, attr) VALUES ( 'PostgreSQL Tutorial', '"paperback" => "243", "publisher" => "postgresqltutorial.com", "language" => "English", "ISBN-13" => "978-1449370000", "weight" => "11.2 ounces"' );
The data that we insert into the hstore column is a list of comma-separated key =>value pairs. Both keys and values are quoted using double quotes (“”).
Let’s insert one more row.
INSERT INTO books (title, attr) VALUES ( 'PostgreSQL Cheat Sheet', ' "paperback" => "5", "publisher" => "postgresqltutorial.com", "language" => "English", "ISBN-13" => "978-1449370001", "weight" => "1 ounces"' );
Query data from an hstore column
Querying data from an hstore column is similar to querying data from a column with native data type using the
SELECT statement as follows:
SELECT attr FROM books;
Query value for a specific key
Postgresql hstore provides the
-> operator to query the value of a specific key from an hstore column. For example, if we want to know ISBN-13 of all available books in the
books table, we can use the
-> operator as follows:
SELECT attr -> 'ISBN-13' AS isbn FROM books;
Use value in the WHERE clause
You can use the
-> operator in the
WHERE clause to filter the rows whose values of the hstore column match the input value. For example, the following query retrieves the
weight of a book that has
ISBN-13 value matches
SELECT title, attr -> 'weight' AS weight FROM books WHERE attr -> 'ISBN-13' = '978-1449370000';
Add key-value pairs to existing rows
With hstore column, you can easily add a new key-value pair to existing rows e.g., you can add free shipping key to the
attr column of the
books table as follows:
UPDATE books SET attr = attr || '"freeshipping"=>"yes"' :: hstore;
Now, you can check to see if the
"freeshipping" => "yes" pair has been added successfully.
SELECT title, attr -> 'freeshipping' AS freeshipping FROM books;
Update existing key-value pair
You can update existing key-value pair using the
UPDATE statement. The following statement updates the value of the
"freeshipping" key to
UPDATE books SET attr = attr || '"freeshipping"=>"no"' :: hstore;
Remove existing key-value pair
PostgreSQL allows you to remove existing key-value pair from an hstore column. For example, the following statement removes the
"freeshipping"=>"no" key-value pair in the
UPDATE books SET attr = delete(attr, 'freeshipping');
Check for a specific key in hstore column
You can check for a specific key in an hstore column using the
? operator in the
WHERE clause. For example, the following statement returns all rows with attr contains key
SELECT title, attr->'publisher' as publisher, attr FROM books WHERE attr ? 'publisher';
Check for a key-value pair
You can query based on the hstore key-value pair using the
@> operator. The following statement retrieves all rows which
attr column contains a key-value pair that matches
SELECT title FROM books WHERE attr @> '"weight"=>"11.2 ounces"' :: hstore;
Query rows that contain multiple specified keys
You can query the rows whose hstore column contain multiple keys using
?& operator. For example, you can get books where
attr column contains both
SELECT title FROM books WHERE attr ?& ARRAY [ 'language', 'weight' ];
To check if a row whose hstore column contains any key from a list of keys, you use the
?| operator instead of the
Get all keys from an hstore column
To get all keys from an hstore column, you use the
akeys() function as follows:
SELECT akeys (attr) FROM books;
Or you can use the
skey() function if you want PostgreSQL to return the result as a set.
SELECT skeys (attr) FROM books;
Get all values from an hstore column
Like keys, you can get all values from an hstore column using the
avals() function in the form of arrays.
SELECT avals (attr) FROM books;
Or you can use the
svals() function if you want to get the result as a set.
SELECT svals (attr) FROM books;
Convert hstore data to JSON
PostgreSQL provides the
hstore_to_json() function to convert hstore data to JSON. See the following statement:
SELECT title, hstore_to_json (attr) json FROM books;
Convert hstore data to sets
To convert hstore data to sets, you use the
each() function as follows:
SELECT title, (EACH(attr) ).* FROM books;
In this tutorial, we have shown you how to work with PostgreSQL hstore data type and introduced you to the most useful operations that you can perform against the hstore data type.