Postgres Hstore

PostgreSQL hstore refers to an extension that provides access to key-value storage for the PostgreSQL database. It allows us to store the key-value pairs similar to the Redis database within a single PostgreSQL column. This is an incredible feature when storing the data with dynamic attributes or properties in a particular row.

In this tutorial, we will walk you throw how to enable and use the hstore values in PostgreSQL in simple steps.

Enable the Hstore Extension

To store the hstore values in PostgreSQL, we need to enable the hstore extension. In PostgreSQL 9.1 and above, the hstore extension is installed by default; hence, you only need to activate it.

To enable the hstore extension in PostgreSQL, run the following query:

Once we enable the extension, we can create a table with an hstore column and start storing the key-value pairs.

Store the Hstore Data

The following example demonstrates how we can use the CREATE TABLE clause in PostgreSQL to create a table with an hstore column:

In the given example, we create a table called my_table with two columns: id and data. The data column has an hstore data type which allows us to store the key-value pairs.

As demonstrated, we can insert the data into the table with a set of key-value pairs which are enclosed in single quotes.

Finally, to retreive the rows from the table, we can run the following query:

This should return the stored values as demonstrated in the following:

Query a Specific Key

If we have an hstore column, we can use the -> operator to query the value of a specific key from the column.

For example:

In the given example, we use the -> operator to retrieve the value that is associated with the “key1” key from the data column.

The result includes a column named “value” that contains the corresponding value.

Output:

Alternatively, the #> operator can access the nested keys within the hstore column.

An example is as follows:

The given example requires a nested key-value pair.

Add a Key-Value Pair

In PostgreSQL, we can quickly and easily add a new key-value pair to an existing hstore column using the “||” operator. This allows us to concatenate a new key-value pair with an existing hstore value.

An example is as follows:

This should add a new key and value pairs.

Run the following select statement on the table:

SELECT * FROM my_table;

The given query returns the following:

Check a Specific Key

PostgreSQL allows us to check if a specific key exists on the hstore column using the “?” operator in the WHERE clause.

An example is as follows:

Output:

In the given example, we use the “?” operator to check if the “key1” key exists in the data column of the my_table table.

The result includes a column named key_exists that indicates whether the key is present (true) or not (false).

Check the Key-Value Pair

We can also check for a key-value pair using the “@>” operator. An example is as follows:

Fetch All Keys

To get all the keys for a given hstore column, we can use the akeys() function as shown in the following example:

Resulting Output:

You can also use the skeys() column to fetch the keys as a set value.

Output:

Fetch All Values

As you can guess, we can use the avals() function to fetch all the values that are stored in a given hstore column as shown in the following example query:

Output:

To fetch the values as a set, use the svals() function.

Convert Hstore to Json

We can convert the data that is stored in an hstore column into JSON using the hstore_to_json() function.

An example is as follows:

The given query should convert the provided hstore to a JSON data as follows:

Conclusion

We covered the hstore extension in PostgreSQL. We explored how we can enable the hstore extensions, how to create the PostgreSQL hstore columns, access the values, modify the hstore values, and more.