intro
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.
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:
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;
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:
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…”
This will open the following popup window:
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 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:
Let’s see some of them in action in real-world examples.
If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.
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:
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:
Adding/updating key-value pairs in an hstore field
These are the username and config columns from the user with an id of 4:
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:
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:
The Functions of hstore
PostgreSQL offers several functions for hstore-based data. The most relevant ones are:
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:
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:
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:
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:
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:
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.
