hstore — Key-Value

hstore in DB9 is a metadata-only shim — CREATE EXTENSION hstore succeeds and registers the extension, along with an hstore type name in pg_type, but DB9 does not implement hstore values. Any use of the type — a column, ALTER TABLE ... ADD COLUMN, a cast, a function argument or result type, a domain, a composite type, a view — fails with ERROR: type "hstore" is not supported (0A000) and the hint Use jsonb instead., whether or not the extension is registered. A LANGUAGE sql function whose body uses hstore is created, and fails the same way when it is called; a PL/pgSQL function that declares an hstore variable is rejected at CREATE FUNCTION.

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 IF NOT EXISTS hstore;

▶ Run

A registered hstore extension blocks pg_dump

While hstore is registered, pg_dump cannot dump the database. It fails with column "typanalyze" does not exist. Register it only if a tool needs the pg_extension row, and run DROP EXTENSION hstore before you take a dump.

What Is Supported

Feature	Supported
CREATE EXTENSION hstore	Yes — registers extension metadata only
hstore column type	No — type "hstore" is not supported (0A000)
Cast from text	No — 'key=>value'::hstore fails with 0A000
Basic key access (->)	No — no hstore value can be created
Containment (@>)	No — no hstore value can be created

What Is Not Supported

Feature	Status	JSONB Alternative
%, ?, ?&, ?| operators	Not supported	jsonb_exists(), jsonb_exists_all()
hstore || hstore concatenation	Not supported	jsonb || jsonb
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_to_record(...) AS x(col type, ...) in FROM — see below
slice(), skeys(), svals()	Not supported	jsonb operators

To turn a JSONB object into a row, use jsonb_to_record with a column definition list:

SQL

SELECT * FROM jsonb_to_record('{"key":"k","value":"v"}'::jsonb) AS x(key text, value text);

jsonb_populate_record(), json_populate_record() and jsonb_populate_recordset() are not available. Given a composite type created with CREATE TYPE kv AS (key text, value text), SELECT * FROM jsonb_populate_record(NULL::kv, '{"key":"k"}'::jsonb) fails with ERROR: function jsonb_populate_record(public.kv, jsonb) does not exist (42883), and the same call in the SELECT list fails with 42883 too. A table’s row type does not help: NULL::my_table casts, but the call then fails the same way, with function jsonb_populate_record(public.my_table, jsonb) does not exist (42883).

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 (values not supported)	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	Not supported (0A000) — metadata shim only
Operators available	None — no hstore value can be created
Recommended alternative	JSONB — full operator support

Next Steps

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