PostgreSQL hstore Data Type: In-Depth Explanation

As I always say, PostgreSQL is one of the best database systems out there in the world because of the features it provides. In this tutorial, we will see one of the most incredible features of PostgreSQL: hstore datatype. Let’s see what is it and how it can be used.

Also Read: PostgreSQL – Data Types

Introduction to hstore in PostgreSQL

If you know the hashmap in Java or the map in JavaScript, congratulations! You now also know the hstore!

Similar to the hashmap, the hstore allows us to store the sets of key-value pairs as a single value in the column. This means, instead of using two different columns for key and value data, you can use a single column to store key-value pairs using the hstore.

Note that, hstore has unique keys just like maps but can have duplicate values. If you try to insert duplicate key-value pair, PostgreSQL will overwrite the previous value of that key with the new value.

This can be very useful when the data is semistructured or records with many attributes that are rarely examined.

However, remember that the hstore is just a text string that contains multiple key-value pairs.

Now let’s see the syntax of hstore and some examples of it.

Get Started with hstore in PostgreSQL

First thing is, you need to enable the hstore in your PostgreSQL using the below command, otherwise, you will get an error.

CREATE EXTENSION hstore;

Now let’s see one basic example of the hstore.

select '"a"=>"1","b"=>"2"'::hstore;
select '"a"=>"1","a"=>"2"'::hstore;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In the above example, we wrote two queries. The first query has two key-value pairs with unique key names, whereas the second query also has two key-value pairs but with the same key names.

Therefore, the hstore will not create two different key-value pairs in the second query. Let’s see the output and you will understand it better.

As you can see, the second query only outputs a single key-value pair because hstore can’t contain duplicate keys.

Create Table Having hstore Type

Now let’s try creating a table having an hstore column.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    price NUMERIC(10, 2),
    metadata HSTORE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, we have created a products table that will hold the data of products. Each product may have different metadata such as colour, price etc. Therefore, we will use the hstore type to store the metadata of products.

Now let’s insert some records into the table.

INSERT INTO products (name, price, metadata)
VALUES ('Apple iPhone 13', 999.99, '"color" => "blue", "storage" => "128GB"');

INSERT INTO products (name, price, metadata)
VALUES ('Samsung Galaxy S21 Ultra', 1199.99, '"color" => "black", "storage" => "256GB", "camera" => "108MP"');

INSERT INTO products (name, price, metadata)
VALUES ('Sony PlayStation 5', 499.99, '"storage" => "825GB", "resolution" => "4K"');

INSERT INTO products (name, price, metadata)
VALUES ('Microsoft Surface Laptop 4', 899.99, '"color" => "silver", "storage" => "512GB", "memory" => "16GB"');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that, both keys and values are written inside the double quotes (” “) and multiple key-value pairs are separated using commas.

Let’s see the table output.

SELECT * FROM products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Perfect!

Let’s now see some operators and functions associated with the hstore datatype.

Query Data From hstore Column Using -> Operator

Postgresql provides us with the -> operator to access the values from the hstore data. You just have to write the column name followed by the -> operator and then the key name. Let’s see it in action.

SELECT name, price, metadata->'color' as Color, 
metadata->'storage' as Storage FROM products;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the records which don’t have the colour key will show the empty value in the output. Let’s check it.

As you can see, we got the product’s name, price, colour and storage.

Add Key-Value Pairs to the Existing Record

To add a new key-value pair to the existing record, we can use the UPDATE statement with the concatenate operator(||) provided by PostgreSQL.

Let’s see how we can add a new key-value pair.

UPDATE products
SET metadata= metadata || '"ram"=>"16GB"';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, we have assigned the concatenated value to the column name. Here, the old value will be concatenated with the new key-value pair and assigned back to the original column.

Let’s see the output.

Update Value of Existing Key-Value Pairs

If you want to update the key-value pair’s value, you can use the above-mentioned concatenate operator. Note that, an hstore datatype can’t hold duplicate keys, therefore if you try to concatenate a new key-value pair that already exists in the hstore data, PostgreSQL will overwrite it with the new value. Let’s try.

UPDATE products
SET metadata= metadata || '"ram"=>"32GB"'
WHERE id=3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Query Data By Checking if the Key Exists

Let’s say you want to display only those records on which a specific key exists, such as a colour or resolution. In this case, you can use the (?) operator with the where clause.

Let’s see how it works.

SELECT id, name, metadata 
FROM products WHERE
metadata ? 'color';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In our table, the record of id 3 doesn’t have a colour key in its metadata column. Therefore, the output will skip the record number 3. Let’s check the output.

Remove Key-Value Pairs From the Record

There are a bunch of functions PostgreSQL provides us for the hstore type. One of those is the delete() function to delete the key-value pair from the existing record.

Let’s see how we can do that.

Here, I want to remove the memory key from the hstore column. So, I will write a query shown below-

UPDATE products
SET metadata= delete(metadata, 'memory');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the delete function removes the given key from the hstore data and returns the remaining data so that it gets assigned back to the column name.

Let’s see the output.

As you can see, the memory key is removed from the records in which it existed.

Conclusion

There are a bunch of operators and functions available to manipulate and play around with the hstore data type. PostgreSQL has taken all the possibilities into consideration while developing the features for the hstore type. Here is the complete list of hstore operators and functions that you can read and try yourself. Most of them are very straightforward and some of those need you to think a little to use them. We highly recommend going through the whole list and trying out all functions and operators.

Reference

PostgreSQL official documentation on hstore.