HSTORE

Introduction to PostgreSQL hstore

The hstore module provides a data type for storing sets of key-value pairs within a single PostgreSQL value. Both keys and values are text strings. hstore is useful for semi-structured data — rows that have many optional attributes that are rarely queried across the whole table.

Before using hstore, enable the extension:

CREATE EXTENSION hstore;

Creating a table with an hstore column

CREATE TABLE books (
  id    SERIAL PRIMARY KEY,
  title VARCHAR(255),
  attr  hstore
);

Inserting hstore data

hstore values are written as comma-separated "key" => "value" pairs:

INSERT INTO books (title, attr) VALUES (
  'PostgreSQL Tutorial',
  '"paperback"  => "243",
   "publisher"  => "postgresqltutorial.com",
   "language"   => "English",
   "ISBN-13"    => "978-1449370000",
   "weight"     => "11.2 ounces"'
);

Querying hstore data

-- Get a specific key's value using ->
SELECT attr -> 'ISBN-13' AS isbn FROM books;

-- Filter by a key's value in WHERE
SELECT title, attr -> 'weight' AS weight
FROM books
WHERE attr -> 'ISBN-13' = '978-1449370000';

-- Check if a key exists using ?
SELECT title FROM books WHERE attr ? 'publisher';

-- Check for a key-value pair using @>
SELECT title FROM books
WHERE attr @> '"weight"=>"11.2 ounces"'::hstore;

-- Check for multiple keys using ?&
SELECT title FROM books
WHERE attr ?& ARRAY['language', 'weight'];

Modifying hstore data

-- Add a new key-value pair
UPDATE books SET attr = attr || '"freeshipping"=>"yes"'::hstore;

-- Update an existing key
UPDATE books SET attr = attr || '"freeshipping"=>"no"'::hstore;

-- Remove a key
UPDATE books SET attr = delete(attr, 'freeshipping');

Getting all keys and values

-- All keys as an array
SELECT akeys(attr) FROM books;

-- All keys as a set
SELECT skeys(attr) FROM books;

-- All values as an array
SELECT avals(attr) FROM books;

-- Convert hstore to JSON
SELECT title, hstore_to_json(attr) AS json FROM books;

-- Expand to key-value rows
SELECT title, (EACH(attr)).* FROM books;

Production tips

• For new schemas, prefer JSONB over hstore — JSONB supports nested data, typed values, and richer operators.
• Create a GIN index on hstore columns for efficient key-existence (?) and containment (@>) queries: CREATE INDEX idx_books_attr ON books USING GIN (attr);
• hstore values are always text — if you need to store numbers, booleans, or nested objects, JSONB is the right choice.
• Use hstore_to_json() when integrating with APIs or clients that expect JSON format.

Reference: PostgreSQL documentation — hstore.