The hstore extension stores a flat map of text keys to text values, including nullable values. It can fit optional descriptive attributes whose shape varies between rows. That flexibility does not remove the need to define important keys, validate values, and choose indexes from actual predicates. Access flags, prices, and credentials deserve deliberate modeling rather than being hidden among supposedly harmless metadata.

Define missing, null, and text

An absent key and a present key with a null value are different states. Extraction with -> returns SQL NULL for both, while ? distinguishes presence and defined checks for a nonnull value. The quoted string “NULL” is ordinary text. A column-level SQL NULL adds another state; declare the map NOT NULL when that distinction is unnecessary.

Keys cannot be null, and their order is not meaningful. Supplying duplicate keys does not create a list: one value survives without a guarantee about which one. Reject ambiguous input before storage when duplicate handling matters. Bind values through a driver’s supported adaptation or parameterized constructors instead of concatenating an hstore literal from untrusted strings. Driver adaptation is a configuration question, not a universal promise that every result is already a dictionary.

Constraints still work

A CHECK can inspect the map and restrict selected attributes. It is therefore incorrect to say hstore cannot enforce valid values. However, SQL CHECK accepts an unknown result, so a nullable extraction alone can accidentally allow missing or null data. Make presence and null policy explicit, then test each boundary. A unique expression index can constrain an extracted key where appropriate.

Ordinary typed columns remain clearer for frequently joined or constrained business fields. Choosing them is about a stronger schema and simpler operations, not an impossibility of checking a map. Adding a new optional key may avoid a column migration, but changing its meaning can still require validation, backfill, client coordination, and index work. Flexibility moves those decisions; it does not erase them.

Match operators and conversions

GIN and GiST support hstore containment and key-existence operators. Extracted-text equality is a different expression and may need its own B-tree expression index. A whole-map GIN does not automatically index every computation on extracted values. Compare the final SQL predicate, selectivity, and actual plan before adding storage and write overhead.

The standard hstore-to-jsonb conversion preserves nonnull values as JSON strings and maps null values to JSON null. It does not infer that the text “12” is a number. There is no built-in symmetric jsonb-to-hstore cast: converting arbitrary nested JSON requires an explicit shape and conversion policy. Neither direction should be described as a free, universally lossless migration. Measure storage on representative data rather than promising hstore is always smaller.

When it breaks

A text price sorts as text unless explicitly converted, and an invalid number can fail that conversion. Promote important fields with a migration that first identifies invalid data. A valid hstore value is not proof that its keys satisfy application rules. Conversely, indexing every attribute without a recurring query can make writes more expensive for no useful gain.

Map operators express changes to keys, but do not promise independent per-key locking or in-place storage updates. Two callers updating one row still need a coherent concurrency policy. Prefer database-side expressions for targeted changes over a stale client-side read followed by replacement of the whole map; that pattern can overwrite unrelated edits.

Three state cards show presence, defined and extraction results; a tier-policy card and a downward hstore-to-JSON conversion preserve text 12 as a string and null as JSON null.
Presence and nonnull checks answer different questions. Conversion preserves text types; exact historical SQL evidence does not establish per-key locking.

Check it quickly

Run this complete SQL in a fresh disposable PostgreSQL 18 database where hstore is available but not yet enabled. Use a role with extension-creation and temporary-table privileges, an extension schema visible on the search path, and the default unlimited gin_fuzzy_search_limit=0; stop on unexpected errors. The transaction installs the extension, creates temporary data, tests failures deliberately, and rolls back everything it created.

BEGIN;
CREATE EXTENSION hstore;
CREATE TEMP TABLE prefs (
  id integer PRIMARY KEY,
  attrs hstore NOT NULL,
  CHECK (NOT (attrs ? 'tier') OR
         (defined(attrs,'tier') AND attrs->'tier' IN ('basic','pro')))
);
INSERT INTO prefs
SELECT g,hstore('theme',CASE WHEN g%100=0 THEN 'dark' ELSE 'light' END)
FROM generate_series(1,10000) AS g;
CREATE INDEX prefs_attrs_gin ON prefs USING gin(attrs);
ANALYZE prefs;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id FROM prefs WHERE attrs @> 'theme=>dark'::hstore;
SELECT attrs ? 'note' AS present,defined(attrs,'note') AS nonnull,
       attrs->'note' AS value
FROM (VALUES (''::hstore),('note=>NULL'::hstore),('note=>"NULL"'::hstore)) s(attrs);
UPDATE prefs SET attrs=attrs || hstore('tier','pro') WHERE id=1;
UPDATE prefs SET attrs=attrs-'theme'::text WHERE id=1;
DO $$ BEGIN
  INSERT INTO prefs VALUES (10001,'tier=>gold');
  RAISE EXCEPTION 'invalid tier accepted';
EXCEPTION WHEN check_violation THEN RAISE NOTICE 'invalid tier rejected';
END $$;
DO $$ BEGIN
  INSERT INTO prefs VALUES (10002,'tier=>NULL');
  RAISE EXCEPTION 'null tier accepted';
EXCEPTION WHEN check_violation THEN RAISE NOTICE 'null tier rejected';
END $$;
SELECT 'count=>12,note=>NULL'::hstore::jsonb AS converted;
DO $$ BEGIN
  EXECUTE 'SELECT ''{"theme":"dark"}''::jsonb::hstore';
  RAISE EXCEPTION 'unexpected reverse cast';
EXCEPTION WHEN cannot_coerce THEN RAISE NOTICE 'no built-in reverse cast';
END $$;
DO $$ BEGIN
  IF (SELECT count(*) FROM prefs WHERE attrs @> 'theme=>dark') <> 100 OR
     (SELECT attrs FROM prefs WHERE id=1) IS DISTINCT FROM 'tier=>pro'::hstore THEN
    RAISE EXCEPTION 'map query or update mismatch';
  END IF;
  IF 'count=>12,note=>NULL'::hstore::jsonb IS DISTINCT FROM
     '{"count":"12","note":null}'::jsonb THEN
    RAISE EXCEPTION 'conversion changed value types';
  END IF;
END $$;
ROLLBACK;

The containment query returns 100 rows. Presence and nonnull flags are false/false for missing, true/false for null, and true/true for the string. Row one ends with only tier=pro. The deletion key has an explicit text cast because the minus operator also accepts another hstore; leaving the literal untyped can select that different overload. Both invalid-tier inserts must produce their expected notices, and the reverse cast must report its absence. Conversion yields a JSON string count and a JSON null note. The final assertions check the containment count, row-one map, and conversion; the presence/nonnull display needs manual comparison, and the exception blocks separately reject unexpected successes. Retained PostgreSQL 18.6 logs include the exact complete SQL fence and deliberate failing controls, not a rerun in this source review. They establish no client transport or concurrent-update result. Exact timings are not pass criteria.

Sources