PostgreSQL HSTORE Data Type

PostgreSQL allows you to store key-value data types using the HSTORE type. It is similar to JSON objects.

PostgreSQL HSTORE data types are very suitable for storing irregular dictionary values, such as electronic product attributes and clothing specifications, etc.

PostgreSQL HSTORE data types are implemented in the hstore module. To use the PostgreSQL HSTORE type, enable the PostgreSQL hstore extension using the following statement first:

CREATE EXTENSION hstore;

PostgreSQL HSTORE syntax

To create a column of HSTORE data type, use the following syntax:

column_name HSTORE column_constraint

The value of the HSTORE data type takes the following format:

"key1=>value1"[, "key2=>value2", ...]

Explanation:

• "key1=>value1"is a key-value pair. Double quotes can be omitted if the key and value do not contain spaces.
• Use commas to separate multiple key-value pairs.
• Both keys and values ​​are text values.

PostgreSQL HSTORE Examples

Create a new table named product for demonstration:

CREATE TABLE product (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  product_name VARCHAR NOT NULL,
  attributes HSTORE
);

The product table stores the products of an online store, and it consists of three columns:

1. The id column is the primary key column that identifies each row, it is an identity column.
2. The product_name column stores the name of the product and its type is VARCHAR.
3. The attributes column is of HSTORE data type, it cannot be null.

The attributes of products are diverse and are suitable for using HSTORE data types, such as:

• Computer products have attributes such as CPU, memory, hard disk, brand, appearance, etc.
• Clothing products have attributes such as season, style, gender, brand, color, etc.

Insert HSTORE values

To insert data into a HSTORE column, you must ensure that the data is in the correct format. The following INSERT statement inserts two new rows into the product table.

INSERT INTO product (product_name, attributes)
VALUES
  ('Computer A', 'CPU=>2.5, Memory=>16G, Disk=>1T'),
  ('Shirt B', 'Season=>Spring, Style=>Business, Color=>White')
RETURNING *;

 id | product_name |                        attributes
----+--------------+-----------------------------------------------------------
  1 | Computer A   | "CPU"=>"2.5", "Disk"=>"1T", "Memory"=>"16G"
  2 | Shirt B      | "Color"=>"White", "Style"=>"Business", "Season"=>"Spring"
(2 rows)

Qeury HSTORE values

To query HSTORE values, use a SELECT statement like this:

SELECT * FROM product;

 id | product_name |                        attributes
----+--------------+-----------------------------------------------------------
  1 | Computer A   | "CPU"=>"2.5", "Disk"=>"1T", "Memory"=>"16G"
  2 | Shirt B      | "Color"=>"White", "Style"=>"Business", "Season"=>"Spring"
(2 rows)

If you want to query a specific key of HSTORE data, use the -> operator or column_name['key_name'] syntax, as follows:

SELECT
  id,
  product_name,
  attributes -> 'Memory' memory,
  attributes['Disk'] disk
FROM product;

 id | product_name | memory |  disk
----+--------------+--------+--------
  1 | Computer A   | 16G    | 1T
  2 | Shirt B      | <null> | <null>
(2 rows)

To use the key value of the HSTORE data in the WHERE condition, use the column_name['key_name'] syntax:

SELECT *
FROM product
WHERE attributes['Memory'] = '16G';

 id | product_name |                 attributes
----+--------------+---------------------------------------------
  1 | Computer A   | "CPU"=>"2.5", "Disk"=>"1T", "Memory"=>"16G"
(1 row)

To check if the HSTORE data contains a specific key, use the ? operator as follows:

SELECT *
FROM product
WHERE attributes ? 'Color';

 id | product_name |                        attributes
----+--------------+-----------------------------------------------------------
  2 | Shirt B      | "Color"=>"White", "Style"=>"Business", "Season"=>"Spring"
(1 row)

Or use the IS NOT NULL operator, as follows:

SELECT *
FROM product
WHERE attributes['Color'] IS NOT NULL;

 id | product_name |                        attributes
----+--------------+-----------------------------------------------------------
  2 | Shirt B      | "Color"=>"White", "Style"=>"Business", "Season"=>"Spring"
(1 row)

Add key-value pairs to HSTORE data

To add key-value pairs to an existing HSTORE data, use this UPDATE statement:

UPDATE product
SET attributes['Brand'] = 'HP'
WHERE id = 1
RETURNING *;

 id | product_name |                         attributes
----+--------------+------------------------------------------------------------
  1 | Computer A   | "CPU"=>"2.5", "Disk"=>"1T", "Brand"=>"HP", "Memory"=>"16G"
(1 row)

Delete key-value pairs from HSTORE data

To remove an existing key-value pair from the HSTORE data, use the UPDATE statement and delete() function :

UPDATE product
SET attributes = delete(attributes, 'brand')
WHERE id = 1
RETURNING *;

 id | product_name |                 attributes
----+--------------+---------------------------------------------
  1 | Computer A   | "CPU"=>"2.5", "Disk"=>"1T", "Memory"=>"16G"
(1 row)

Update values in HSTORE data

To update values ​​in HSTORE data, use this UPDATE statement:

UPDATE product
SET attributes['Brand'] = 'Dell'
WHERE id = 1
RETURNING *;

 id | product_name |                          attributes
----+--------------+--------------------------------------------------------------
  1 | Computer A   | "CPU"=>"2.5", "Disk"=>"1T", "Brand"=>"Dell", "Memory"=>"16G"
(1 row)

In this tutorial, we showed you how to use the PostgreSQL hstore datatype and introduced you to the most useful operations you can perform on the hstore datatype.

Conclusion

PostgreSQL HSTORE data types are used to store values of key-value pair type.