Efficiently Managing Unstructured Data with PostgreSQL hstore

PostgreSQL’s hstore data type offers an efficient way to store key-value pairs in a single column, ideal for semi-structured and unstructured data. This article outlines the basics of hstore, how to enable it, and practical usage examples.

What Is hstore in PostgreSQL?

hstore allows storing key-value pairs in a single column, making it suitable for user preferences, settings, or metadata.

ALTER TABLE users ADD COLUMN metadata hstore;

Enable hstore with this command:

CREATE EXTENSION hstore;

Below are some 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.
• || , concatenates hstores.
• @>, checks for key-value pairs.
• ?, checks for a key.

Below is an example of how to retrieve language key values:

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

FAQs About the hstore Data Type

Can an hstore column contain duplicated keys?

No, each key must be unique within an hstore column. If a duplicate key is inserted, the new value will overwrite the old one.

What is the difference between hstore and JSONB?

hstore stores flat key-value pairs and supports basic data types, while JSONB supports complex nested structures and a broader range of data types.

What databases support hstore?

hstore is native to PostgreSQL. Other databases might offer similar functionalities but not natively.

What types of data can be stored in an hstore?

hstore stores strings and NULL values as key-value pairs. Complex data must be converted to string format.

Can hstore be considered NoSQL?

While PostgreSQL is not a NoSQL database, hstore provides NoSQL-like capabilities by allowing unstructured data storage.

Conclusion

PostgreSQL’s hstore is a versatile option for storing unstructured data. Its ease of use and powerful features make it a valuable tool for database management. For more information, read the article Storing Unstructured Data With Hstore in PostgreSQL.