HSTORE Data Type in PostgreSQL

The hstore module provides a data type for storing sets of text key-value pairs within a single PostgreSQL column. Both keys and values are text strings. hstore is useful for semi-structured rows with many optional attributes that vary per record — think product specifications where each product type has different fields.

Before using hstore, enable the extension once per database:

CREATE EXTENSION IF NOT EXISTS hstore;

Syntax

column_name hstore

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

'"color" => "red", "size" => "M", "weight_g" => "250"'

Practical Example

Store variable product specifications in an hstore column:

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  sku         VARCHAR(50) NOT NULL UNIQUE,
  name        VARCHAR(200) NOT NULL,
  specs       hstore
);

INSERT INTO products (sku, name, specs) VALUES
  ('BOLT-TI-M6', 'Titanium M6 Bolt',
   '"material"=>"titanium","thread"=>"M6","length_mm"=>"16","grade"=>"5"'),
  ('CABLE-USB-1M', 'USB-C Cable 1m',
   '"length_m"=>"1","connector"=>"USB-C","max_watts"=>"60"'),
  ('FILTER-HEPA', 'HEPA Air Filter',
   '"microns"=>"0.3","efficiency_pct"=>"99.97","size"=>"standard"');

Querying hstore Data

-- Get a specific key's value using ->
SELECT sku, specs -> 'material' AS material
FROM products;

-- Filter by a key's value
SELECT sku, name
FROM products
WHERE specs -> 'material' = 'titanium';

-- Check if a key exists using ?
SELECT sku FROM products WHERE specs ? 'grade';

-- Containment: check for a specific key-value pair
SELECT sku FROM products
WHERE specs @> '"connector"=>"USB-C"'::hstore;

-- Check for multiple keys using ?&
SELECT sku FROM products
WHERE specs ?& ARRAY['material', 'grade'];

Modifying hstore Data

-- Add or update a key-value pair using || (concat)
UPDATE products
SET specs = specs || '"in_stock"=>"yes"'::hstore
WHERE sku = 'BOLT-TI-M6';

-- Update an existing key
UPDATE products
SET specs = specs || '"in_stock"=>"no"'::hstore
WHERE sku = 'BOLT-TI-M6';

-- Remove a key
UPDATE products
SET specs = delete(specs, 'in_stock')
WHERE sku = 'BOLT-TI-M6';

Getting All Keys and Values

-- All keys as an array
SELECT sku, akeys(specs) AS all_keys FROM products;

-- Expand to key-value rows
SELECT sku, (EACH(specs)).key, (EACH(specs)).value
FROM products;

-- Convert to JSON
SELECT sku, hstore_to_json(specs) AS specs_json FROM products;

GIN Indexing for Performance

Create a GIN index for key-existence (?) and containment (@>) queries:

CREATE INDEX CONCURRENTLY idx_products_specs
ON products USING GIN (specs);

Testing with Vela

Before adding an hstore column to an existing production table and migrating attribute data from multiple columns, test the migration on a database branch. This lets you verify that the UPDATE ... SET specs = hstore(...) backfill produces correct values for every row, that the GIN index builds successfully, and that application queries return expected results — all without touching the live table.

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 that are frequently searched with ? or @>: CREATE INDEX idx ON products USING GIN (specs);
• hstore values are always text — if you need numbers, booleans, or nested objects, JSONB is the right choice.
• Use hstore_to_json() or hstore_to_jsonb() when integrating with APIs or clients that expect JSON format.
• Inspect all keys present across a table with: SELECT DISTINCT skeys(specs) FROM products ORDER BY 1;