Skip to content

hstore

data type for storing sets of (key, value) pairs
Repository
https://www.postgresql.org/docs/current/hstore.html
https://www.postgresql.org/docs/current/hstore.html

Overview

PackageVersionCategoryLicenseLanguage
hstore1.8TYPEPostgreSQLC
IDExtensionBinLibLoadCreateTrustRelocSchema
3970hstoreNoYesNoYesYesNo-
Relatedpgbson collection jsonschema pgjq jsquery pg_jsonschema intarray arraymath pgpdf pglite_fusion
Depended Byflux format hstore_hash_ops hstore_ops hstore_pllua hstore_plluau hstore_plperl hstore_plperlu hstore_plpython2u hstore_plpython3u hstore_plpythonu hstore_plruby json_enhancements_with_hstore mbus meta_triggers numhstore pg_auditor pg_readme pg_rowalesce pg_utility_trigger_functions pg_xenophile sys_syn_dblink

Version

PG18PG17PG16PG15PG14
1.81.81.81.81.8

Install

Note: This is a built-in contrib extension of PostgreSQL

CREATE EXTENSION hstore;

Usage

hstore: key-value pair data type

The hstore extension provides a key/value pair data type for storing semi-structured data in a single column.

CREATE EXTENSION hstore;

Basic Usage

SELECT 'name => Alice, age => 30'::hstore;
SELECT 'name => Alice'::hstore -> 'name';           -- Alice
SELECT 'a => 1, b => 2'::hstore ? 'a';              -- true
SELECT 'a => 1'::hstore || 'b => 2'::hstore;        -- "a"=>"1", "b"=>"2"

Operators

OperatorDescriptionExample
->Get value by keyh -> 'key'
||Concatenateh1 || h2
?Contains keyh ? 'key'
?&Contains all keysh ?& ARRAY['a','b']
?|Contains any keyh ?| ARRAY['a','b']
@>Containsh @> 'a=>1'
<@Contained byh <@ 'a=>1, b=>2'
-Delete key(s)h - 'key' or h - ARRAY['a','b']

Subscript Access

SELECT h['name'] FROM mytable;
UPDATE mytable SET h['age'] = '31';

Functions

-- Construction
SELECT hstore('key', 'value');
SELECT hstore(ARRAY['a','b'], ARRAY['1','2']);
SELECT hstore(ROW(1, 'hello'));

-- Extraction
SELECT akeys(h);                    -- text[] of keys
SELECT avals(h);                    -- text[] of values
SELECT skeys(h);                    -- set of keys
SELECT svals(h);                    -- set of values
SELECT each(h);                     -- set of (key, value) records

-- Query
SELECT exist(h, 'key');             -- boolean
SELECT defined(h, 'key');           -- true if non-NULL value

-- Modification
SELECT delete(h, 'key');
SELECT slice(h, ARRAY['a','b']);    -- subset of keys

-- JSON conversion
SELECT hstore_to_json(h);
SELECT hstore_to_jsonb(h);
SELECT hstore_to_json_loose(h);    -- distinguishes numbers/booleans

-- Record conversion
SELECT populate_record(NULL::my_table, h);

Index Support

CREATE INDEX idx ON t USING gin (h);    -- supports @>, ?, ?&, ?|
CREATE INDEX idx ON t USING gist (h);   -- supports @>, ?, ?&, ?|
CREATE INDEX idx ON t USING btree (h);  -- supports =
CREATE INDEX idx ON t USING hash (h);   -- supports =

Was this page helpful?

Last Modified 2026-08-09 · update extension count (24575456)