hstore — Key-Value

hstore in DB9 is a metadata-only shim — CREATE EXTENSION hstore succeeds and registers extension metadata, but the hstore type itself does not exist. Any attempt to use hstore as a column type or cast to hstore will fail with ERROR: type "hstore" does not exist.

For new code, use JSONB instead. JSONB provides a superset of hstore functionality with full operator support, better performance, and broad ecosystem support.

Installation

SQL

CREATE EXTENSION hstore;

▶ Run

What Is Supported

Feature	Supported
CREATE EXTENSION hstore	Yes — registers extension metadata only
hstore column type	No — type "hstore" does not exist
Cast from text	No — 'key=>value'::hstore fails with type error
Basic key access (->)	No — type does not exist
Containment (@>)	No — type does not exist

What Is Not Supported

Feature	Status	JSONB Alternative
%, ?, ?&, `?	` operators	Not supported
`hstore		hstore` concatenation
hs - key deletion	Not supported	jsonb - key
akeys(), avals(), hstore_to_array()	Not supported	jsonb_object_keys(), jsonb_each()
hstore_to_json(), hstore_to_jsonb()	Not supported	Cast directly with ::jsonb
populate_record(), hstore_to_record()	Not supported	jsonb_populate_record()
slice(), skeys(), svals()	Not supported	jsonb operators

Migrate to JSONB

If you have existing code using hstore, JSONB provides equivalent functionality with full operator support:

SQL

-- hstore: CREATE TABLE with hstore column
-- JSONB equivalent:
CREATE TABLE settings (
  id SERIAL PRIMARY KEY,
  data JSONB
);

-- hstore: INSERT 'key=>value' syntax
-- JSONB equivalent:
INSERT INTO settings (data) VALUES ('{"theme": "dark", "lang": "en"}');

-- hstore: data->'key' (returns TEXT)
-- JSONB equivalent:
SELECT data->>'theme' FROM settings;
-- Returns: 'dark'

-- hstore: data @> 'key=>value'
-- JSONB equivalent:
SELECT * FROM settings WHERE data @> '{"theme": "dark"}';

-- hstore: delete a key  data - 'key'
-- JSONB equivalent:
UPDATE settings SET data = data - 'theme';

-- hstore: get all keys  akeys(data)
-- JSONB equivalent:
SELECT jsonb_object_keys(data) FROM settings;

Why JSONB

Feature	hstore (DB9)	JSONB
Key-value storage	Not available (type missing)	Full support
Nested values	No	Yes
Operator support	None	Full
GIN indexing	No	Yes
Schema validation	No	Yes (check constraints)
Ecosystem support	Limited	Universal

Limits

Limit	Value
hstore type	Does not exist — metadata shim only
Operators available	None — type does not exist
Recommended alternative	JSONB — full operator support

Next Steps

• SQL Reference — SQL engine compatibility and data types
• Extensions Overview — All 9 built-in extensions
• Compatibility Matrix — Full supported/unsupported feature surface