PostgreSQL Tutorial: hstore Data Type

August 2, 2023

Summary: in this tutorial, you’ll learn how to work with PostgreSQL hstore data type.

Table of Contents

Introduction to the PostgreSQL hstore 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:

  • id is the primary key that identifies the book.
  • title is 0the title of the products
  • attr stores 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" => "rockdata.net",
        "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" => "rockdata.net",
        "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;

Output:

                                                             attr
------------------------------------------------------------------------------------------------------------------------------
 "weight"=>"11.2 ounces", "ISBN-13"=>"978-1449370000", "language"=>"English", "paperback"=>"243", "publisher"=>"rockdata.net"
 "weight"=>"1 ounces", "ISBN-13"=>"978-1449370001", "language"=>"English", "paperback"=>"5", "publisher"=>"rockdata.net"
(2 rows)

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;

Output:

      isbn
----------------
 978-1449370000
 978-1449370001
(2 rows)

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 title and weight of a book that has ISBN-13 value matches 978-1449370000:

SELECT
	title, attr -> 'weight' AS weight
FROM
	books
WHERE
	attr -> 'ISBN-13' = '978-1449370000';

Output:

        title        |   weight
---------------------+-------------
 PostgreSQL Tutorial | 11.2 ounces
(1 row)

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;

Output:

         title          | freeshipping
------------------------+--------------
 PostgreSQL Tutorial    | yes
 PostgreSQL Cheat Sheet | yes
(2 rows)

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 "no".

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 attr column.

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 publisher.

SELECT
  title,
  attr->'publisher' as publisher,
  attr
FROM
	books
WHERE
	attr ? 'publisher';

Output:

         title          |  publisher   |                                                             attr
------------------------+--------------+------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL Tutorial    | rockdata.net | "weight"=>"11.2 ounces", "ISBN-13"=>"978-1449370000", "language"=>"English", "paperback"=>"243", "publisher"=>"rockdata.net"
 PostgreSQL Cheat Sheet | rockdata.net | "weight"=>"1 ounces", "ISBN-13"=>"978-1449370001", "language"=>"English", "paperback"=>"5", "publisher"=>"rockdata.net"
(2 rows)

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 "weight"=>"11.2 ounces".

SELECT
	title
FROM
	books
WHERE
	attr @> '"weight"=>"11.2 ounces"' :: hstore;

Output:

        title
---------------------
 PostgreSQL Tutorial
(1 row)

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 language and weight keys.

SELECT
	title
FROM
	books
WHERE
	attr ?& ARRAY [ 'language', 'weight' ];

Output:

         title
------------------------
 PostgreSQL Tutorial
 PostgreSQL Cheat Sheet
(2 rows)

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 the akeys() function as follows:

SELECT
	akeys (attr)
FROM
	books;

Output:

                     akeys
-----------------------------------------------
 {weight,ISBN-13,language,paperback,publisher}
 {weight,ISBN-13,language,paperback,publisher}
(2 rows)

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

SELECT
	skeys (attr)
FROM
	books;

Output:

   skeys
-----------
 weight
 ISBN-13
 language
 paperback
 publisher
 weight
 ISBN-13
 language
 paperback
 publisher
(10 rows)

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;

Output:

                          avals
---------------------------------------------------------
 {"11.2 ounces",978-1449370000,English,243,rockdata.net}
 {"1 ounces",978-1449370001,English,5,rockdata.net}
(2 rows)

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

SELECT
	svals (attr)
FROM
	books;

Output:

     svals
----------------
 11.2 ounces
 978-1449370000
 English
 243
 rockdata.net
 1 ounces
 978-1449370001
 English
 5
 rockdata.net
(10 rows)

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;

Output:

         title          |                                                              json
------------------------+--------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL Tutorial    | {"weight": "11.2 ounces", "ISBN-13": "978-1449370000", "language": "English", "paperback": "243", "publisher": "rockdata.net"}
 PostgreSQL Cheat Sheet | {"weight": "1 ounces", "ISBN-13": "978-1449370001", "language": "English", "paperback": "5", "publisher": "rockdata.net"}
(2 rows)

Convert hstore data to sets

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

SELECT
	title,
	(EACH(attr) ).*
FROM
	books;

Output:

         title          |    key    |     value
------------------------+-----------+----------------
 PostgreSQL Tutorial    | weight    | 11.2 ounces
 PostgreSQL Tutorial    | ISBN-13   | 978-1449370000
 PostgreSQL Tutorial    | language  | English
 PostgreSQL Tutorial    | paperback | 243
 PostgreSQL Tutorial    | publisher | rockdata.net
 PostgreSQL Cheat Sheet | weight    | 1 ounces
 PostgreSQL Cheat Sheet | ISBN-13   | 978-1449370001
 PostgreSQL Cheat Sheet | language  | English
 PostgreSQL Cheat Sheet | paperback | 5
 PostgreSQL Cheat Sheet | publisher | rockdata.net
(10 rows)

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.

See more

PostgreSQL Tutorial: Data Types

PostgreSQL Documentation: hstore extension

Search

Categories

Tags