Postgres HStore data type and how to use it

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 you store strings; it does not provide nesting. It’s sort of like a flat key-value pair datatype.

Enable the PostgreSQL hstore extension.

In some cases, the hstore might not be enabled for your Postgres database. To enable it, run the following command below:

CREATE EXTENSION hstore;

Creating and Adding HStore Fields

Create a table with the hstore data type.

To create an hstore field, you just need to define the field as an hstore type while creating your table.

CREATE TABLE products (
	id serial primary key,
	title VARCHAR (255),
	attr hstore
);

Insert data into the PostgreSQL hstore column.

To insert any data into the hstore field. You pass your key-value pairs as a string. With the ⇒ 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 or 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 or just parts of it. For that, a 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 the 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 the existing key-value pair.

To remove a key from your Postgres hstore field, you can use the delete method 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 them.

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 from the field.

SELECT
	attr
FROM
	products;

Query value of a specific key - -> operator

To get the 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 the 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, which will convert the hstore key-value pairs into a JSON field.

SELECT
  title,
  hstore_to_json (attr) json
FROM
  products;

I hope you were able to find value in this blog and understand the basics of the 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

- Jitendra Nirnejak