Storing Unstructured Data With Hstore in PostgreSQL

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

---

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 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;

Enabling the hstore extension in DbVisualizer.

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!

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. Right-click on the “Tables” voice menu related to the public schema of your database and select “Create Table…”

The "Create Table..." DbVisualizer option.

This will open the following popup window:

Creating a new table in DbVisualizer. Note the "hstore" data type.

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:

Note the data format of the hstore data stored in the "config" column.

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. 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:

Testing the -> query in DbVisualizer.

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:

Note the result of the -> query.

Adding/updating key-value pairs in an hstore field

These are the username and config columns from the user with an id of 4:

Retrieving the info of the user with id = 4 in DbVisualizer.

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.

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:

Note that the resulting table contains the desired data.

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:

Retrieving the info of the user with id = 7 in DbVisualizer.

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:

Note that "language" key-value pair was deleted successfully.

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:

Note that the resulting row is an array of hstore keys.

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:

Note that the resulting row is an array of hstore values.

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. DbVisualizer is a complete, fully-featured, advanced database client that comes with powerful features, such as query explain plan and ER charting support! Download it for free now!

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.