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?
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
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:
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 )
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
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.
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.
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:
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
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
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;
:: operator used to cast the string after
'"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
The user with an
id will no longer have a key-value pair with “language” key in the
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:
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?
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?
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?
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.