Timescale Logo

PostgreSQL Extensions: hstore

​hstore is a powerful and flexible PostgreSQL extension that allows developers to use the database as a schema-less NoSQL store while still retaining the benefits of a traditional relational database.

It provides the ability to store key-value pairs in a single PostgreSQL value. This is particularly useful for storing semi-structured data and datasets where the number of attributes (keys) can vary from record to record.

​Installing the hstore Extension

​Before you can use the hstore extension, you need to install it. Here's how:

1. First, connect to the PostgreSQL database where you want to install the hstore extension. You can do this using the psql command-line client or a graphical client like pgAdmin. On Timescale, you can find available extensions by going to Operations > Extensions from your service overview, which will also give you installation instructions.​

2. Once connected, run the following SQL command:

CREATE EXTENSION IF NOT EXISTS hstore;​

This command will install the hstore extension if it's not already installed.

Using the hstore Extension

To use the hstore extension, you need to create a column of type hstore in your table. Here's an example:

CREATE TABLE products (
    id serial PRIMARY KEY,
    attributes hstore
);
In this example, the attributes column can store any number of key-value pairs. You can insert data into the attributes column like this:

INSERT INTO products (attributes) VALUES ('"color"=>"blue", "size"=>"XL"');
You can also query the data in an hstore column using various operators and functions provided by the hstore extension. For example, you can find all products that have a "color" key like this:

SELECT * FROM products WHERE attributes ? 'color';

​Time-Series Use Cases for hstore​

The hstore extension is particularly useful for time-series data where the structure of the data can change over time. For example, you might be tracking various metrics for a set of machines, and the set of metrics can change as you add or remove sensors from the machines.​

With hstore, you can easily add or remove attributes without having to change the schema of your database. This makes it a great fit for time-series data.

​Using hstore with Timescale and time-series data

​Timescale is a time-series database built on top of PostgreSQL. It provides advanced features for handling time-series data, like automatic partitioning and aggregation.​

You can use the hstore extension with Timescale to store time-series data with a flexible schema. Just like with a regular PostgreSQL database, you can create a table with an hstore column and insert your time-series data into it.

Here's an example:

CREATE TABLE machine_metrics (
    time TIMESTAMPTZ NOT NULL,
    machine_id int NOT NULL,
    metrics hstore,
    PRIMARY KEY(time, machine_id)
);

In this example, the metrics column can store any number of key-value pairs, allowing you to easily add or remove metrics as needed.

​hstore also works on hypertables, which allows you to get all of the functionality of TimescaleDB with the flexibility of hstore. For example, you can turn machine_metrics above into a hypertable by running:

SELECT create_hypertable('machine_metrics','time');
​

Learn More

To learn more about Timescale and hypertables, check out our getting started guide.

Timescale Logo

Products

Launch 2023Time Series and AnalyticsTimescale VectorEnterprise TierDynamic PostgreSQL
Cloud statusSupportSecurityCloud Terms of Service

Learn

DocumentationBlogForumTutorials
Release notesSuccess storiesTime series database

Company

Contact usCareersAboutNewsroom
BrandCommunityTimescale shopCode of conduct

Subscribe to the Timescale Newsletter

By submitting, I acknowledge Timescale’s Privacy Policy
2024 © Timescale Inc. All rights reserved.
Privacy preferencesLegalPrivacySitemap