Hstore is one of the first unstructured data types supported in the PostgreSQL Database. It was added in Postgres 8.3 as a way to help you store and access key-value pairs of data in your database. In terms of functionality, hstore is a bit limited compared to something like JSON. Hstore in Postgres only lets your store strings, it does not provide nesting. It’s sort of like a flat key-value pair datatype.
Enable PostgreSQL hstore extension
In some cases, the hstore might not be enabled for your Postgres database. To enable it to run the following command below.
CREATE EXTENSION hstore;
Creating and Adding HStore Fields
Create a table with hstore data type
To create an hstore field you just need to define the field as hstore
type while creating your table.
CREATE TABLE products (
id serial primary key,
title VARCHAR (255),
attr hstore
);
Insert data into PostgreSQL hstore column.
To insert any data into the hstore field. You pass your key-value pairs as a string. With ⇒ symbol defining the relation between the key and the value. Please refer to the example below to understand it.
INSERT INTO books (title, attr)
VALUES
(
'iPhone 14 Pro',
'"type" => "phone",
"company" => "apple",
"price" => "999",
);
Update
Since you can have multiple values/attributes inside a single field the update option for the Postgres hstore fields needs more options. You may not always want to update the entire field and just the parts of it. For that, few operators and options let you manipulate your Postgres hstore data.
Add key-value pairs to existing rows
To add another key-value pair to an existing hstore field in Postgres you can use the ||
operator to add the key-value pair and then use the ::
operator to convert the result into an hstore type.
UPDATE products
SET attr = attr || '"storage"=>"128"' :: hstore;
Update existing key-value pair
To update an existing key-value pair use a similar approach, just use the existing key and it will automatically replace the old value with the new one.
UPDATE products
SET attr = attr || '"price"=>"1099"' :: hstore;
Remove existing key-value pair
To remove a key from your Postgres hstore field you can use the delete methor provided by PostgreSQL, in the first argument pass the hstore field and then the attribute or key you want to remove.
UPDATE products
SET attr = delete(attr, 'store');
Query
Since the hstore field can contain unstructured data and key-value pairs, we need some special operators to access this data. Let’s go through some of them to understand the different use cases and queries you can perform with it.
Query data from an hstore column
To get the value of the field you can perform a simple query and mention the field. It’ll give you the raw data of the field.
SELECT
attr
FROM
products;
Query value of a specific key - ->
operator
To get a value of a specific key you can use the ->
operator, it’ll give you the value of that exact key.
SELECT
attr -> 'company' AS company
FROM
products;
Use value in the WHERE clause
The ->
operator can be used inside the WHERE clause as well.
SELECT
title, attr -> 'price' AS price
FROM
products
WHERE
attr -> 'price' < '1000';
Check for a specific key in hstore column - ?
Operator
To know if a key exists inside an hstore field. You can use the?
operator and mention the key you’re checking for. If the key exists the expression will be true.
SELECT
title,
attr->'company' as company,
attr
FROM
products
WHERE
attr ? 'company';
Query rows that contain multiple specified keys - ?&
operator
To check against a set of keys, you can build an array of the keys and check against the hstore field with the ?&
operator. If all the keys in the array are in the Postgres hstore field then only it’ll be true.
SELECT
title
FROM
products
WHERE
attr ?& ARRAY [ 'price', 'company' ];
Check for a key-value pair - @>
operator
To check if a key-value pair exists in an hstore field you can use the @>
operator.
SELECT
title
FROM
products
WHERE
attr @> '"company"=>"apple"' :: hstore;
Get all keys from an hstore column
To get all the keys present in a Postgres hstore field you can use the akeys
method provided by Postgres and pass the hstore field as an argument.
SELECT
akeys (attr)
FROM
products;
Get all values from an hstore column
To get all the values present in a Postgres hstore field you can use the avals
method provided by Postgres and pass the hstore field as an argument.
SELECT
avals (attr)
FROM
products;
Convert hstore data to JSON
If you need to convert your hstore data to JSON you can use the hstore_to_json
method and it’ll convert the hstore key-value pairs into a JSON field.
SELECT
title,
hstore_to_json (attr) json
FROM
products;
Hope you were able to find value in this blog and able to understand the basics of hstore data type in the Postgres database. I will recommend you check the Postgres documentation to learn more about it. I’m also adding the link to all the supported hstore operators and functions, you can check that out too.
Useful Links
Postgres hstore documentation - https://www.postgresql.org/docs/current/hstore.html
Postgres hstore operators and functions - https://www.postgresql.org/docs/current/hstore.html#id-1.11.7.27.6