Enable PostgreSQL hstore extension

Before working with the hstore data type, you need to enable the hstore extension which loads thecontrib module to your PostgreSQL instance.

The following statement creates the hstore extension:

CREATE EXTENSION hstore;

Create a table with hstore data type

We create a table namedbooks that has three columns:

• id is the primary key that identifies the book.
• title is the title of the products
• attr stores attributes of the book such as ISBN, weight, and paperback. The data type of theattr column is hstore.

We use theCREATE TABLE statement to create thebooks table as follows:

CREATE TABLE books (idserial primary key,titleVARCHAR (255),attr hstore);

Insert data into the PostgreSQL hstore column

The followingINSERT statement inserts data into the hstore column:

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 theSELECT statement as follows:

SELECT attrFROM 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 thebooks table, we can use the-> operator as follows:

SELECTattr-> 'ISBN-13' AS isbnFROMbooks;

Use value in the WHERE clause

You can use the-> operator in theWHERE clause to filter the rows whose values of the hstore column match the input value. For example, the following  query retrieves thetitle and weight of a book that hasISBN-13 value matches 978-1449370000:

SELECTtitle, attr-> 'weight' AS weightFROMbooksWHEREattr-> 'ISBN-13' = '978-1449370000';

Add key-value pairs to existing rows

With a hstore column, you can easily add a new key-value pair to existing rows e.g., you can add a free shipping key to theattr column of thebooks table as follows:

UPDATE booksSET attr= attr|| '"freeshipping"=>"yes"' :: hstore;

Now, you can check to see if the"freeshipping" => "yes" pair has been added successfully.

SELECTtitle,        attr-> 'freeshipping' AS freeshippingFROMbooks;

Update existing key-value pair

You can update the existing key-value pair using theUPDATE statement. The following statement updates the value of the"freeshipping" key to"no".

UPDATE booksSET 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 theattr column.

UPDATE booksSET 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 theWHERE clause. For example, the following statement returns all rows with attr contains keypublisher.

SELECT  title,  attr->'publisher' as publisher,  attrFROMbooksWHEREattr ?'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 whoseattr column contains a key-value pair that matches"weight"=>"11.2 ounces".

SELECTtitleFROMbooksWHEREattr @> '"weight"=>"11.2 ounces"' :: hstore;

Query rows that contain multiple specified keys

You can query the rows whose hstore column contains multiple keys using?& operator. For example, you can get books whereattr column contains bothlanguage andweight keys.

SELECTtitleFROMbooksWHEREattr ?&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?& operator.

Get all keys from an hstore column

To get all keys from an hstore column, you use theakeys() function as follows:

SELECTakeys (attr)FROMbooks;

Or you can use the skey() function if you want PostgreSQL to return the result as a set.

SELECTskeys (attr)FROMbooks;

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.

SELECTavals (attr)FROMbooks;

Or you can use the  svals() function if you want to get the result as a set.

SELECTsvals (attr)FROMbooks;

Convert hstore data to JSON

PostgreSQL provides thehstore_to_json() function to convert hstore data toJSON. See the following statement:

SELECT  title,  hstore_to_json (attr)jsonFROM  books;

Convert hstore data to sets

To convert hstore data to sets, you use the  each() function as follows:

SELECTtitle,(EACH(attr) ).*FROMbooks;

In this tutorial, we have shown you how to work with the PostgreSQL hstore data type and introduced you to the most useful operations that you can perform against the hstore data type.

Last updated onFebruary 1, 2024

On this page

• Enable PostgreSQL hstore extension
• Create a table with hstore data type
• Insert data into the PostgreSQL hstore column
• Query data from an hstore column
• Query value for a specific key
• Use value in the WHERE clause
• Add key-value pairs to existing rows
• Update existing key-value pair
• Remove existing key-value pair
• Check for a specific key in hstore column
• Check for a key-value pair
• Query rows that contain multiple specified keys
• Get all keys from an hstore column
• Get all values from an hstore column
• Convert hstore data to JSON
• Convert hstore data to sets