The hstore data type

Eugenia Kuzina

The hstore data type allows you to store key/value pairs in a single value in PostgreSQL. hstore is useful when you cannot predefine keys, or keys can vary for different objects.

The hstore module implements the hstore data type. You need to create the hstore extension to use this type:

CREATE EXTENSION hstore;

The hstore value includes key => value pairs separated by commas. Keys and values are stored as text strings.

Create the products table with an hstore column to see how to work with this type:

CREATE TABLE products (
  id serial PRIMARY KEY,
  name character varying(100),
  attributes hstore
);

In this table, different products can have different attributes. Add the following values to the table:

INSERT INTO products (name, attributes) VALUES (
 'The Time Machine',
 'author => "Herbert Wells",
  pages => 202,
  genre => "sci-fi"'),
('Hyperion',
 'author => "Dan Simmons",
  pages => 640,
  genre => "sci-fi"'),
('The Great Gatsby',
'author => "F. Scott Fitzgerald",
  pages => 256,
  genre => "novel"'),
('People',
 'publisher => "Time Inc",
  number => 38,
  date => "09.12.2022"'
);

The table content:

 id |       name       |                    attributes
----+------------------+-----------------------------------------------------------------
  1 | The Time Machine | "genre"=>"sci-fi", "pages"=>"202", "author"=>"Herbert Wells"
  2 | Hyperion         | "genre"=>"sci-fi", "pages"=>"640", "author"=>"Dan Simmons"
  3 | The Great Gatsby | "genre"=>"novel", "pages"=>"256", "author"=>"F. Scott Fitzgerald"
  4 | People           | "date"=>"09.12.2022", "number"=>"38", "publisher"=>"Time Inc"

The hstore type has the following syntax specifics:

  • The order of hstore pairs is not significant and may not be reproduced on output.

  • PostgreSQL ignores whitespaces between pairs or around the => sign. Keys and values that include whitespace, commas, =, or > characters should be double-quoted. To include a double quote (") or a backslash (\) in a key or value, escape it with a backslash (\", \\).

  • Each key should be unique.

  • A value can contain SQL NULL (key => NULL). The null keyword is not case-sensitive. Double-quote it to process as the "NULL" string value.

Operations with hstore values

PostgreSQL provides a variety of operators and functions to work with the hstore type. This section gives examples of typical operations that you can perform with this functionality. For more information on hstore operators and function, refer to the PostgreSQL documentation: Hstore operators and functions.

Get value by key

You can use the -> operator to retrieve a value of a specific key from an hstore column. If the key is missing, a corresponding row is not included in the result:

SELECT name, attributes->'author' as author
FROM products;

The result:

       name       |       author
------------------+---------------------
 The Time Machine | Herbert Wells
 Hyperion         | Dan Simmons
 The Great Gatsby | F. Scott Fitzgerald

You can also utilize the -> operator in the WHERE clause. The following statement selects products of the sci-fi genre:

SELECT name, attributes->'author' as author
FROM products
WHERE attributes -> 'genre' = 'sci-fi';

The result:

       name       |    author
------------------+---------------
 The Time Machine | Herbert Wells
 Hyperion         | Dan Simmons

Add a key/value pair to an existing hstore value

You can add a new key/value pair to existing rows. For this, use the || operator that concatenates two hstore values. The following statement adds "freeshipping"=>"yes" to all existing rows:

UPDATE products
SET attributes = attributes || '"freeshipping"=>"yes"' :: hstore;

Update a key/value pair

To update an existing key/value pair, utilize the same || operator. For example, the following statement updates the freeshipping value of the Hyperion book with no.

UPDATE products
SET attributes = attributes || '"freeshipping"=>"no"' :: hstore
WHERE name = 'Hyperion';

The table content:

 id |       name       |                                        attributes
----+------------------+------------------------------------------------------------------------------------------
  1 | The Time Machine | "genre"=>"sci-fi", "pages"=>"202", "author"=>"Herbert Wells", "freeshipping"=>"yes"
  2 | Hyperion         | "genre"=>"sci-fi", "pages"=>"640", "author"=>"Dan Simmons", "freeshipping"=>"no"
  3 | The Great Gatsby | "genre"=>"novel", "pages"=>"256", "author"=>"F. Scott Fitzgerald", "freeshipping"=>"yes"
  4 | People           | "date"=>"09.12.2022", "number"=>"38", "publisher"=>"Time Inc", "freeshipping"=>"yes"

Add multiple key/value pairs

To add several key/value pairs, use a two-dimensional array that contains keys and values. The following statement replaces the attributes field value in the row where id = 4:

UPDATE products SET attributes = hstore(ARRAY[['pages', '32'],['media', 'ebook']]) WHERE id = 4;

The query below adds values to the attributes field value in the row where id = 4:

UPDATE products SET attributes = attributes || hstore(ARRAY[['pages', '32'],['media', 'ebook']]) WHERE id = 4;

Remove a key/value pair

Use the delete function to remove a key/value pair from the hstore column:

UPDATE products
SET attributes = delete(attributes, 'freeshipping');

You can also utilize an array to delete several keys:

UPDATE products
SET attributes = delete(attributes, ARRAY['genre', 'pages']);

Determine if a key exists

Use the ? operator to check if a specific key exists in the hstore column:

SELECT name, attributes -> 'publisher' as publisher
FROM products
WHERE attributes ? 'publisher';

The result:

  name  | publisher
--------+-----------
 People | Time Inc

Check if a key/value pair exists

To check if a key/value pair exists, utilize the @> operator that determines whether the left operand contains the right one. The following statement retrieves all rows where the attributes column contains '"freeshipping"⇒"yes"':

SELECT name, attributes -> 'freeshipping' as freeshipping
FROM products
WHERE attributes @> '"freeshipping"=>"yes"' :: hstore;

The result:

       name       | freeshipping
------------------+--------------
 The Time Machine | yes
 The Great Gatsby | yes
 People           | yes

Get rows that contain multiple specified keys

Use the ?& operator to get rows with the several specified keys. The following statement returns rows with the genre and pages keys:

SELECT name FROM products
WHERE attributes ?& ARRAY [ 'genre', 'pages' ];

The result:

       name
------------------
 The Time Machine
 The Great Gatsby
 Hyperion

Get all keys

Use the akeys function to get all keys from an hstore column as an array:

SELECT akeys (attributes) FROM products;

The result:

                akeys
--------------------------------------
 {genre,pages,author,freeshipping}
 {genre,pages,author,freeshipping}
 {date,number,publisher,freeshipping}
 {genre,pages,author,freeshipping}

Utilize the skeys function to get all keys as a set of records:

SELECT skeys (attributes) FROM products;

The result:

 genre
 pages
 author
 freeshipping
 genre
 pages
 author
 freeshipping
 date
 number
 publisher
 freeshipping
 genre
 pages
 author
 freeshipping

Get all values

Use the avals function to get all values from an hstore column as an array:

SELECT avals (attributes) FROM products;

The result:

                avals
---------------------------------------
 {sci-fi,202,"Herbert Wells",yes}
 {novel,256,"F. Scott Fitzgerald",yes}
 {09.12.2022,38,"Time Inc",yes}
 {sci-fi,640,"Dan Simmons",no}

Utilize the svals function to get all keys as a set of records:

SELECT svals (attributes) FROM products;

The result:

 sci-fi
 202
 Herbert Wells
 yes
 novel
 256
 F. Scott Fitzgerald
 yes
 09.12.2022
 38
 Time Inc
 yes
 sci-fi
 640
 Dan Simmons
 no

Convert hstore data to a set of records

Use the each function to extract hstore data as a set of records:

SELECT
    name,
    (each(attributes) ).*
FROM products;

The result:

       name       |     key      |        value
------------------+--------------+---------------------
 The Time Machine | genre        | sci-fi
 The Time Machine | pages        | 202
 The Time Machine | author       | Herbert Wells
 The Time Machine | freeshipping | yes
 The Great Gatsby | genre        | novel
 The Great Gatsby | pages        | 256
 The Great Gatsby | author       | F. Scott Fitzgerald
 The Great Gatsby | freeshipping | yes
 People           | date         | 09.12.2022
 People           | number       | 38
 People           | publisher    | Time Inc
 People           | freeshipping | yes
 Hyperion         | genre        | sci-fi
 Hyperion         | pages        | 640
 Hyperion         | author       | Dan Simmons
 Hyperion         | freeshipping | no

Convert hstore data to JSON/JSONB

PostgreSQL provides the hstore_to_json and hstore_to_jsonb functions to convert hstore data to the JSON or JSONB format. The following statement converts the attributes column to JSONB:

SELECT hstore_to_jsonb (attributes) jsonb
FROM products;

The result:

                                           jsonb
--------------------------------------------------------------------------------------------
 {"genre": "sci-fi", "pages": "202", "author": "Herbert Wells", "freeshipping": "yes"}
 {"genre": "novel", "pages": "256", "author": "F. Scott Fitzgerald", "freeshipping": "yes"}
 {"date": "09.12.2022", "number": "38", "publisher": "Time Inc", "freeshipping": "yes"}
 {"genre": "sci-fi", "pages": "640", "author": "Dan Simmons", "freeshipping": "no"}

Create indexes on the hstore field

You can use indexes to reduce the time PostgreSQL takes to look up particular keys and values. The hstore type supports GIN and GiST indexing with @>, ?, ?&, and ?| operators.

The following statements create the GIN and GiST indexes on the attributes column:

CREATE INDEX idx_gin_attributes ON products USING GIN(attributes);

CREATE INDEX idx_gist_attributes ON products USING GIST(attributes);

You can also use the hash and btree indexes with the = operator.

The following statements create the hash and btree indexes on the attributes column:

CREATE INDEX idx_btree_attributes ON products USING BTREE(attributes);

CREATE INDEX idx_hash_attributes ON products USING HASH(attributes);
Found a mistake? Seleсt text and press Ctrl+Enter to report it