Storing Unstructured Data With Hstore in PostgreSQL
Published: 2023-08-03 07:00:00

**Let's learn everything you need to know about the hstore data type. You will find out what hstore is, why PostgreSQL supports the hstore data type, and how to use it in many examples.**

---
Tools used in this tutorial
DbVisualizer, top rated database management tool and SQL client
[The PostgreSQL Database](https://www.postgresql.org/)

---
In PostgreSQL, the term “hstore” refers to a data type that allows you to store key-value pairs in a single cell. `hstore` is particularly useful for storing semi-structured and unstructured data in a table column. With `hstore`, you can directly query and update specific key-value pairs, without having to parse the entire data structure.

Although it may not be the best solution for storing very complex data, `hstore` is a powerful tool for managing semi-structured and unstructured data in your PostgreSQL database. In this article, you will understand what the `hstore` data type represents in PostgreSQL, how to enable it via the `hstore` extension, and how to use it in several scenarios.

Follow this guide and become an expert on the `hstore` PostgreSQL data type!

What Is hstore in PostgreSQL?
In PostgreSQL, `hstore` is a data type to store key-value pairs in string format in a single column. This makes `hstore` a good data type for storing unstructured and semi-structured data, such as user preferences, configuration settings, or metadata.

`hstore` is a flexible data type that supports several operators and functions. Also, `hstore` supports indexing. This can greatly improve the performance of queries involving `hstore` columns. Also, `hstore` makes it possible to simplify the data model and reduce the need for complex relational structures.

The textual representation of an `hstore` used for input and output includes one or more `"key=>value"` pairs separated by commas. Here are some examples of valid hstores:

- points=>106
- "language"=>"English"
- "theme"=>"dark", "language"=>"English"
- "theme"=>"dark", ui=>advanced

You can add a `metadata` column of type `hstore` to the `users` table in PostgreSQL with a query like so:

```
1 ALTER TABLE users ADD COLUMN metadata hstore

```
So, you can use `hstore` like any other PostgreSQL data type.

Let’s now better understand how to use the `hstore` data type with some examples. Before doing that, though, you need to enable the `hstore` extension.

## Enabling the PostgreSQL hstore Extension
PostgreSQL comes with [several disabled modules](https://www.postgresql.org/docs/9.1/contrib.html) that allow you to extend the core PostgreSQL system. These modules include porting tools, plug-in functions, and analysis utilities. Even though some of them are very useful, they are initially disabled because they usually target a limited audience or specific use cases. `hstore` is one of those modules.

So, working with the `hstore` data type, you first need to enable the `hstore` PostgreSQL module. Enable the `hstore` extension in a PostgreSQL database by launching the below query:

```
1 CREATE EXTENSION hstore;

```
<br />
![Enabling the hstore extension in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jn2uux2jfviue95z1ysu.png)
<figure><figcaption>Enabling the hstore extension in DbVisualizer.</figcaption></figure>
<br />
You can now use the `hstore` data type!

## hstore in Action
Let’s take a look at some `hstore` examples to understand how to deal with it in PostgreSQL. All the following examples involve running queries. To make everything easier, you should equip yourself with an advanced database client, [such as DbVisualizer](https://www.dbvis.com/)!

### Creating a new table with hstore columns
Assume you want to create a table called `users` with the following four columns:

- `id`: The primary key.
- `username`: The name specified by the user when they subscribed.
- `password`: The hash of the password chosen by the user when they subscribed.
- `config`: It contains unstructured data in key-value format. 

This is an `hstore` column, and it involves config data such as the weekly point goal, preferred language, UI colors, and more.
Create the `users` table with the `CREATE TABLE` statement below:

```
1 CREATE TABLE users(
2    id serial PRIMARY KEY,
3    username VARCHAR (255) NOT NULL,
4    password VARCHAR (255) NOT NULL,
5    config hstore
6 )
```
Keep in mind that in DbVisualizer you can [create a table visually](https://confluence.dbvis.com/display/UG100/Creating+a+Table+-+basics?_gl=1*1lizd66*_ga*NjI4NzAzMTQ5LjE2MzUxNDg0MTM.*_ga_PTZV59MQGC*MTY4Nzg2NjAyOS43OTYuMS4xNjg3ODY4MDM1LjU1LjAuMA..&_ga=2.230948447.1159816461.1687764293-628703149.1635148413). Right-click on the “Tables” voice menu related to the `public` schema of your database and select “Create Table…”
<br />
![The "Create Table..." DbVisualizer option.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ct0dlzybs4du6k92fcwo.png)
<figure><figcaption>The "Create Table..." DbVisualizer option.</figcaption></figure>
<br />
This will open the following popup window:
<br />
![Creating a new table in DbVisualizer. Note the "hstore" data type.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nbgd58a8prwedkegir3r.png)
<figure><figcaption>Creating a new table in DbVisualizer. Note the "hstore" data type.</figcaption></figure>
<br />
As you can see, here you can visually define what data your new table will contain. Note that DbVisualizer natively supports all PostgreSQL data types, including `hstore`.

### Inserting data into an hstore column
You can use the `INSERT` statement to add a new row to the `users` table involving `hstore` columns as below:

```
1 INSERT INTO users(
2    id,
3    username,
4    password,
5    config
6 )  VALUES (
7    DEFAULT,
8    'ThunderStrike',
9   '$2y$10$Y8X1QL4q1MxKPKbUK05iB.8vSgS70l.Ktbb24IBtT1dXvzTtTmVEy'
10    "points" => "879",
11    "language" => "Danish",
12    "bg-color" => "#FF5733",
13    "theme" => "dark"'
14 )
```
To enter data into an `hstore` column, you must use a string containing a list of comma-separated `key=>value` pairs. To make your query more readable consider wrapping both the keys and values with double or single quotes. Extra space characters will be ignored, and you can use them to better indent your query.

### Select data from an hstore column
You can select data from an `hstore` column just like you can query data from any other column in PostgreSQL. Use a `SELECT` statement to query an hstore column as below:

```
1 SELECT config FROM users
```
The query above will return the following result:
<br />
![Note the data format of the hstore data stored in the "config" column.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n6r6564phg9qs4qlrjyq.png)
<figure><figcaption>Note the data format of the hstore data stored in the "config" column.</figcaption></figure>
<br />
Note that the data contained in the `config` column of type `hstore` is in the order as they were entered with.

## hstore Operators
PostgreSQL provides [several operators to deal with `hstore` data type.](https://www.postgresql.org/docs/current/hstore.html#HSTORE-OP-TABLE) The most important ones are:

`->`: To extract a single key-value pair from an `hstore` column.
`||`: To concatenate two `hstores`.
`@>`: To check if an `hstore` column contains a set of key-value pairs.
`?`: To check if an `hstore` column contains a specific key.
`?&`: To check if an `hstore` column contains the specified keys in an array.
Let’s see some of them in action in real-world examples.

### Selecting a hstore value for a specific key
Assume you want to retrieve the values associated with the “language” key of the `config` column in the `users` table. You can achieve this with the `->` operator as follows:

```
1 SELECT config-> 'Language' AS language
2 FROM users
```
This would return:
<br />
![Testing the -> query in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8yqe7d8xmokjo5q67a5r.png)
<figure><figcaption>Testing the -> query in DbVisualizer.</figcaption></figure>
<br />
Note the `NULL` values on `config` cells that do not contain the “language” key.

### Filtering by values an hstore column in a WHERE clause
Now, if you want to retrieve all users with “Spanish” as the preferred language, you can use the -> operator in a WHERE clause:

```
1 SELECT
2    username,
3    config -> 'language' AS language
4 FROM
5    users
6 WHERE
7    config -> 'language' = 'Spanish';
```
This query will return the two users who set Spanish as their favorite language:
<br />
![Note the result of the -> query.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l9lx096qd9i3smrklm19.png)
<figure><figcaption>Note the result of the -> query.</figcaption></figure>
<br />

### Adding/updating key-value pairs in an hstore field
These are the `username` and `config` columns from the user with an `id` of 4:
<br />
![Retrieving the info of the user with id = 4 in DbVisualizer.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wulv0891kskbgaxo2f4f.png)
<figure><figcaption>Retrieving the info of the user with id = 4 in DbVisualizer.
</figcaption></figure>
<br />
You can use the `||` operator to merge a set of key-value pairs to the `hstore` data contained in that `config` column as below:

```
1 UPDATE
2    users
3 SET
4    config = config || '"bg-color"=>"#00DD66", "language"=>"Italian"' :: hstore
5 WHERE
6   
7 id = 4;
```
Note the `::` operator used to cast the string after `||` to `hstore` data.

The `'"bg-color"=>"#00DD66"'` is new and will be added to the `config` column. Instead, the `'"language"=>"Italian"'` key-value pair will overwrite the existing key-value.

Let’s verify that in DbVisualizer:

![Note the new config value.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y9xeyn8gau5isgwbm3o9.png)
<figure><figcaption>Note the new config value.</figcaption></figure>
<br />

### Verify if an hstore column contains a key-value pair

You can take advantage of the `@>` operator to get all users that have the `"'language"=>"Italian"'"` key-pair in their `config` column by running a query like the one below:

```
1 SELECT
2    *
3 FROM
4    users
5 WHERE
6
7 config @> '"language"=>"Italian"' :: hstore
```
This will return the two users who configured “Italian” as the preferred language:
<br />
![Note that the resulting table contains the desired data.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/upc8d2wzafktw8yc0p0j.png)
<figure><figcaption>Note that the resulting table contains the desired data.
</figcaption></figure>
<br />

## The Functions of hstore
PostgreSQL offers several functions for `hstore`-based data. The most relevant ones are:

- `delete()`: To remove an existing key-value pair from an `hstore` by key.
- `akeys()`: To get the list of keys contained in an `hstore` as an array.
- `avals()`: To get the list of values contained in an `hstore` as an array.
- `hstore_to_json()`: To convert all non-null values from an `hstore` to JSON strings.
- `slice()`: To extract a subset of an `hstore` containing only the specified keys.

Let’s take a look at a few examples involving some of these functions.

### Removing an existing key-value pair from hstore
This is the info related to the user with an `id` of 7:
<br />
![Retrieving the info of the user with id = 7 in DbVisualizer.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/48t0frpwlsa9rojnjxc8.png)
<figure><figcaption>Retrieving the info of the user with id = 7 in DbVisualizer.
</figcaption></figure>
<br />
You can remove the key-value pair associated with the "language" key with the `delete()` function as follows:

```
1 UPDATE
2    users
3 SET
4    config = delete(config, 'language');
5
```
Note that you need an `UPDATE` query, not a `DELETE`.

The user with an `id` will no longer have a key-value pair with “language” key in the `config` column:
<br />
![Note that "language" key-value pair was deleted successfully.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mk9qzmrl9ohepq6lhwyl.png)
<figure><figcaption>Note that "language" key-value pair was deleted successfully.
</figcaption></figure>
<br />

### Getting all keys from an hstore column
Suppose you want to get all keys contained in the `config` column of the user with `id=1`. You can achieve that result with the `akeys()` function as below:

```
1 SELECT
2    akeys(config)
3 FROM
4    users
5 WHERE
6    id=1;
```

In DbVisualizer, this returns:
<br />
![Note that the resulting row is an array of hstore keys.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/htrh3csbnqyfupwhd91g.png)
<figure><figcaption>Note that the resulting row is an array of hstore keys.
</figcaption></figure>
<br />

### Get all values from an hstore column
You can use the `avals()` function to get the values of the `config` column in array format as follows:

```
1 SELECT
2    avals(config)
3 FROM
4    users;
```

If you execute the query in DbVisualizer, you would get:
<br />
![Note that the resulting row is an array of hstore values.
](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jh7n3cnlsw45cisef51j.png)
<figure><figcaption>Note that the resulting row is an array of hstore values.
</figcaption></figure>
<br />

## Conclusion
In this article, you understood what `hstore` is in PostgreSQL, what benefits this data type can bring, and how to enable it. In particular, you learned that `hstore` is a data type for storing key-value pairs and must be enabled with a PostgreSQL extension. Thus, you can use `hstore` to store unstructured data, as you saw here in many examples.

`hstore` is a complex data type and dealing with it is not easy. Luckily, hstore is one of the several data types supported by DbVisulizer as [a PostgreSQL client](https://www.dbvis.com/database/postgresql/). DbVisualizer is a complete, fully-featured, advanced database client that comes with powerful features, such as [query explain plan](https://www.dbvis.com/feature/explain-plan/) and [ER charting](https://www.dbvis.com/feature/references-graphs/) support! [Download it for free now!](https://www.dbvis.com/download/)

## FAQs About the hstore Data Type

### Can an hstore column contain duplicated keys?
**No, an hstore column cannot contain duplicate keys.** The `hstore` data type in PostgreSQL is designed to store a set of unique key-value pairs. Thus, each key is unique and associated with a single value. If you try to insert a key-value pair into an `hstore` column that has the same key in an existing pair, the new value will simply overwrite the old one.

### What is the difference between hstore and JSONB?
`hstore` and `JSONB` are two PostgreSQL data types that allow you to store key-value pairs. The main differences between them are that:

- `hstore` stores key-value pairs as a flat string and only supports basic data types such as strings and null values.
- `JSONB` stores JSON data in a binary format and supports nested data structures, arrays, and complex data types.

`Thus, JSONB is more flexible and powerful than hstore.` At the same time, `JSONB` may be slower than `hstore` for certain types of queries.

### What databases support hstore?
The `hstore` data type is a native PostgreSQL data type. It was introduced in PostgreSQL 8.3, and all later versions support `hstore` natively. Other databases do not support `hstore` natively, but some DBMSs offer comparable data types or extensions that provide similar functionality.

### What types of data can be stored in an hstore?
The `hstore` data type in PostgreSQL can store a set of key-value pairs. Specifically, each value is string or `NULL`. This means that `hstore` cannot store complex data, unless first converting the value involving complex data structure to string.

### Can hstore be considered NoSQL?
Even though PostgreSQL cannot be considered a NOSQL technology, `hstore` can be seen as a sort of NoSQL solution in PostgreSQL. This is because NoSQL databases are designed to handle unstructured data that does not fit well into the traditional relational database model, and `hstore` is a key-value PostgreSQL data type that can be used to store unstructured data.


## About the author
Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.