How to Use hstore Extension in PostgreSQL: Syntax and Examples

To use the hstore extension in PostgreSQL, first enable it with CREATE EXTENSION hstore;. Then you can create columns of type hstore to store sets of key-value pairs in a single column, and use functions like hstore() to manipulate them.

Syntax

The hstore extension allows you to store sets of key-value pairs in a single column. You enable it with CREATE EXTENSION hstore;. To create a table with an hstore column, use CREATE TABLE table_name (column_name hstore);. You can insert data using the hstore() function or the 'key => value' syntax.

sql

CREATE EXTENSION hstore;

CREATE TABLE example (
  id serial PRIMARY KEY,
  data hstore
);

INSERT INTO example (data) VALUES ('"key1"=>"value1", "key2"=>"value2"');

Example

This example shows how to enable the hstore extension, create a table with an hstore column, insert key-value pairs, and query the data.

sql

CREATE EXTENSION IF NOT EXISTS hstore;

CREATE TABLE products (
  id serial PRIMARY KEY,
  attributes hstore
);

INSERT INTO products (attributes) VALUES
  ('"color"=>"red", "size"=>"M"'),
  ('"color"=>"blue", "size"=>"L", "weight"=>"1kg"');

-- Query all products
SELECT * FROM products;

-- Get the color of the first product
SELECT attributes -> 'color' AS color FROM products WHERE id = 1;

Output

 id |           attributes           
----+-------------------------------
  1 | "color"=>"red", "size"=>"M"
  2 | "color"=>"blue", "size"=>"L", "weight"=>"1kg"

 color 
-------
 red
(1 row)

Common Pitfalls

Common mistakes include forgetting to enable the hstore extension before using it, inserting data with incorrect syntax, and confusing hstore with JSON types. Also, keys and values must be quoted properly when inserting. Using hstore functions without casting can cause errors.

sql

/* Wrong: Using hstore without enabling extension */
-- INSERT INTO example (data) VALUES ('"key"=>"value"'); -- ERROR

/* Right: Enable extension first */
CREATE EXTENSION IF NOT EXISTS hstore;

/* Wrong: Missing quotes around keys or values */
-- INSERT INTO example (data) VALUES ('key=>value'); -- ERROR

/* Right: Proper quotes */
INSERT INTO example (data) VALUES ('"key"=>"value"');

Quick Reference

Command	Description
CREATE EXTENSION hstore;	Enable the hstore extension in the current database
CREATE TABLE t (col hstore);	Create a table with an hstore column
INSERT INTO t VALUES ('"key"=>"value"');	Insert key-value pairs into hstore column
SELECT col -> 'key' FROM t;	Retrieve the value for a key from hstore
col || '"newkey"=>"newvalue"'	Add or update a key-value pair in hstore

Key Takeaways

Always enable the hstore extension with CREATE EXTENSION before using it.

Use hstore columns to store sets of key-value pairs efficiently in one column.

Insert data with properly quoted keys and values using '"key"=>"value"' syntax.

Use hstore operators like -> to access values by key.

Avoid confusing hstore with JSON; they have different syntax and functions.