Mastering PostgreSQL hstore for Unstructured Data

Mastering PostgreSQL hstore for Unstructured Data

DbVisualizer's photo
DbVisualizer
·

2 min read

The hstore data type in PostgreSQL is designed to store key-value pairs, making it ideal for managing semi-structured and unstructured data. This article introduces hstore, explains how to enable it, and provides examples of its use.

What Is hstore in PostgreSQL?

hstore is used to store key-value pairs in a single column, simplifying data models by reducing the need for complex relational structures, see example below.

ALTER TABLE users ADD COLUMN metadata hstore;

Activate the hstore extension with the code below.

CREATE EXTENSION hstore;

Below are useful code examples of how to create a table with hstore, insert data and querying data.

CREATE TABLE users(
    id serial PRIMARY KEY,
    username VARCHAR (255) NOT NULL,
    password VARCHAR (255) NOT NULL,
    config hstore
);

INSERT INTO users (username, password, config)
VALUES ('ThunderStrike', '$2y$10$Y8X1QL4q1MxKPKbUK05iB...',
        '"points"=>"879", "language"=>"Danish", "bg-color"=>"#FF5733", "theme"=>"dark"');

SELECT config FROM users;

Key hstore operators

  • >, extracts a key-value pair.

  • ||, merges two hstores.

  • @>, checks if an hstore contains key-value pairs.

  • ?, checks for a specific key.

Retrieve language key values

SELECT config->'language' AS language FROM users;

Conclusion

hstore in PostgreSQL is a robust tool for handling unstructured data. Enabling and using it is simple, making it a valuable addition to your data management toolkit. For further reading, visit the article Storing Unstructured Data With Hstore in PostgreSQL.

hstorePostgreSQL