The hstore extension
Manage key-value pairs in Postgres using hstore
The hstore
extension is a flexible way to store and manipulate sets of key-value pairs within a single Postgres value. It is particularly useful for semi-structured data or data that does not have a rigid schema.
This guide covers the basics of the hstore
extension - how to enable it, how to store and query key-value pairs, and perform operations on hstore data with examples. hstore
is valuable in scenarios where schema-less data needs to be stored efficiently, such as in configurations, application settings, or any situation where the data structure may evolve over time.
note
hstore
is an open-source extension for Postgres that can be installed on any compatible Postgres instance. Detailed installation instructions and compatibility information can be found at PostgreSQL Extensions.
Version availability
Please refer to the list of all extensions available in Neon for up-to-date information.
Currently, Neon uses version 1.8
of the hstore
extension for all Postgres versions.
hstore
extension
Enable the Enable the extension by running the following SQL statement in your Postgres client:
For information about using the Neon SQL Editor, see Query with Neon's SQL Editor. For information about using the psql
client with Neon, see Connect with psql.
Example usage
Creating a table with hstore column
Consider a table that stores the product catalog for an electronics shop. Each product has a name and a set of attributes that describe it. The attributes for each product are not fixed and may change over time. This makes hstore
a good choice for storing this data.
Inserting data
Inserting data into an hstore
column is done by providing a string containing key-value pairs into the column.
hstore
stores both keys and values for each record as strings (values can be nulls). For numeric attributes like price and megapixels, they are cast to strings when inserted into the table.
Querying hstore
data
hstore
columns can be referenced as regular columns in a query. To access the attributes in an hstore
column, we use the ->
operator.
For example, to retrieve the name and brand for all products with price less than 1000, we can run the following query:
Since the price
attribute is stored as a string, we need to cast it to an integer before comparing it to 1000. This query returns the following:
hstore
data
Operators for hstore
offers a variety of operators for manipulating and querying key-value pairs. We go over some examples below.
Check if a key exists
The ?
operator is used to check if an hstore
contains a specific key.
This query returns the following:
Check if an hstore contains another hstore
The @>
operator is used to check if the hstore
on the left contains the right operand. For example, the query below looks for products that have a brand
attribute of Apple
.
This query returns the following:
Concatenating two hstore values
The ||
operator is used to concatenate two hstore
values. For example, the query below updates the attributes for the product with name Laptop
.
To verify, we can run the query below.
This query returns the following:
Check if a hstore contains any of the specified keys
The ?|
operator is used to check if an hstore
contains any of the keys specified in the right operand. For example, the query below returns all products that have either a screen_size
or megapixels
attribute.
This query returns the following:
Hstore
functions
The hstore
extension also adds functions to manipulate the hstore
data. We go over some examples below.
Retrieve all keys
The akeys
function returns an array of all the keys in an hstore
value. For example, the query below returns all the keys for Dell laptop products.
This query returns the following:
Convert hstore to JSON
The hstore_to_json
function converts an hstore
value to JSON
. For example, the query below converts the attributes
column to JSON
for all products with a brand
attribute of Apple
.
Extract all keys and values
The each
function returns the set of key-value pairs for an hstore
value. For example, the query below returns each attribute of the Nikon Camera as a separate row.
This query returns the following:
hstore
with JSON
Comparing The hstore
and JSON
data types can be both used to store semi-structured data. Hstore
has a flat data model — both keys and values must be strings. This makes it more efficient for simple key-value data.
In constrast, JSON
supports a variety of data types, and can also store nested data structures. This makes it more flexible, but trades off some performance.
Indexing and performance
Indexing can improve the performance of queries involving hstore
data, particularly for large datasets.
Hstore
supports the regular btree
and hash
indexes. However, this is only useful for equality comparisons of the entire hstore
value, since these indexes have no knowledge of its substructure.
For queries that involve key-level filtering, like the @>
or the ?
operators, the GIN
and GIST
indexes are more useful. The indexes can be created as shown in this example:
Conclusion
The hstore
extension offers a powerful and flexible way to handle semi-structured data in Postgres. This guide provides an overview of using hstore
, including creating records and querying on its attributes. It also covers some of the common operators and functions available for hstore
data.
Resources
Need help?
Join our Discord Server to ask questions or see what others are doing with Neon. Users on paid plans can open a support ticket from the console. For more detail, see Getting Support.