hstore
data type for storing sets of (key, value) pairs
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
hstore | 1.8 | TYPE | PostgreSQL | C |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 3970 | hstore | No | Yes | No | Yes | Yes | No | - |
| Related | intarray prefix semver unit pgpdf pglite_fusion md5hash asn1oid |
|---|---|
| Depended By | hstore_pllua hstore_plluau hstore_plpython3u pg_readme pg_readme_test_extension |
Version
| PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|
| 1.8 | 1.8 | 1.8 | 1.8 | 1.8 |
Install
Note: This is a built-in contrib extension of PostgreSQL
CREATE EXTENSION hstore;
Usage
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
| Operator | Description | Example |
|---|---|---|
-> | Get value by key | h -> 'key' |
|| | Concatenate | h1 || h2 |
? | Contains key | h ? 'key' |
?& | Contains all keys | h ?& ARRAY['a','b'] |
?| | Contains any key | h ?| ARRAY['a','b'] |
@> | Contains | h @> 'a=>1' |
<@ | Contained by | h <@ '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 =
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.