# F.16. hstore
This module implements the `hstore` data type for storing sets of key/value pairs within a single PostgreSQL value. This can be useful in various scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. Keys and values are simply text strings.
This module is considered “trusted”, that is, it can be installed by non-superusers who have `CREATE` privilege on the current database.
## F.16.1. `hstore` External Representation
The text representation of an `hstore`, used for input and output, includes zero or more *`key`* `=>` *`value`* pairs separated by commas. Some examples:
```
k => v
foo => bar, baz => whatever
"1-a" => "anything at all"
```
The order of the pairs is not significant (and may not be reproduced on output). Whitespace between pairs or around the `=>` sign is ignored. Double-quote keys and values that include whitespace, commas, `=`s or `>`s. To include a double quote or a backslash in a key or value, escape it with a backslash.
Each key in an `hstore` is unique. If you declare an `hstore` with duplicate keys, only one will be stored in the `hstore` and there is no guarantee as to which will be kept:
```
SELECT 'a=>1,a=>2'::hstore;
hstore
----------
"a"=>"1"
```
A value (but not a key) can be an SQL `NULL`. For example:
```
key => NULL
```
The `NULL` keyword is case-insensitive. Double-quote the `NULL` to treat it as the ordinary string “NULL”.
#### Note
Keep in mind that the `hstore` text format, when used for input, applies *before* any required quoting or escaping. If you are passing an `hstore` literal via a parameter, then no additional processing is needed. But if you're passing it as a quoted literal constant, then any single-quote characters and (depending on the setting of the `standard_conforming_strings` configuration parameter) backslash characters need to be escaped correctly. See [Section 4.1.2.1](https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS) for more on the handling of string constants.
On output, double quotes always surround keys and values, even when it's not strictly necessary.
## F.16.2. `hstore` Operators and Functions
The operators provided by the `hstore` module are shown in [Table F.7](https://www.postgresql.org/docs/14/hstore.html#HSTORE-OP-TABLE), the functions in [Table F.8](https://www.postgresql.org/docs/14/hstore.html#HSTORE-FUNC-TABLE).
#### **Table F.7. `hstore` Operators**
| Operator
Description
Example(s)
|
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| hstore -> text → text
Returns value associated with given key, or NULL if not present.
'a=>x, b=>y'::hstore -> 'a' → x
|
| hstore -> text\[] → text\[]
Returns values associated with given keys, or NULL if not present.
'a=>x, b=>y, c=>z'::hstore -> ARRAY\['c','a'] → {"z","x"}
|
| `hstore` |
| hstore ? text → boolean
Does hstore contain key?
'a=>1'::hstore ? 'a' → t
|
| hstore ?& text\[] → boolean
Does hstore contain all the specified keys?
'a=>1,b=>2'::hstore ?& ARRAY\['a','b'] → t
|
| `hstore` `?` |
| hstore @> hstore → boolean
Does left operand contain right?
'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1' → t
|
| hstore <@ hstore → boolean
Is left operand contained in right?
'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL' → f
|
| hstore - text → hstore
Deletes key from left operand.
'a=>1, b=>2, c=>3'::hstore - 'b'::text → "a"=>"1", "c"=>"3"
|
| hstore - text\[] → hstore
Deletes keys from left operand.
'a=>1, b=>2, c=>3'::hstore - ARRAY\['a','b'] → "c"=>"3"
|
| hstore - hstore → hstore
Deletes pairs from left operand that match pairs in the right operand.
'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore → "a"=>"1", "c"=>"3"
|
| anyelement #= hstore → anyelement
Replaces fields in the left operand (which must be a composite type) with matching values from hstore.
ROW(1,3) #= 'f1=>11'::hstore → (11,3)
|
| %% hstore → text\[]
Converts hstore to an array of alternating keys and values.
%% 'a=>foo, b=>bar'::hstore → {a,foo,b,bar}
|
| %# hstore → text\[]
Converts hstore to a two-dimensional key/value array.
%# 'a=>foo, b=>bar'::hstore → {{a,foo},{b,bar}}
|
#### **Table F.8. `hstore` Functions**
| Function
Description
Example(s)
|
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| hstore ( record ) → hstore
Constructs an hstore from a record or row.
hstore(ROW(1,2)) → "f1"=>"1", "f2"=>"2"
|
| hstore ( text\[] ) → hstore
Constructs an hstore from an array, which may be either a key/value array, or a two-dimensional array.
hstore(ARRAY\['a','1','b','2']) → "a"=>"1", "b"=>"2"
hstore(ARRAY\[\['c','3'],\['d','4']]) → "c"=>"3", "d"=>"4"
|
| hstore ( text\[], text\[] ) → hstore
Constructs an hstore from separate key and value arrays.
hstore(ARRAY\['a','b'], ARRAY\['1','2']) → "a"=>"1", "b"=>"2"
|
| hstore ( text, text ) → hstore
Makes a single-item hstore.
hstore('a', 'b') → "a"=>"b"
|
| akeys ( hstore ) → text\[]
Extracts an hstore's keys as an array.
akeys('a=>1,b=>2') → {a,b}
|
| skeys ( hstore ) → setof text
Extracts an hstore's keys as a set.
skeys('a=>1,b=>2') →
|
| avals ( hstore ) → text\[]
Extracts an hstore's values as an array.
avals('a=>1,b=>2') → {1,2}
|
| svals ( hstore ) → setof text
Extracts an hstore's values as a set.
svals('a=>1,b=>2') →
|
| hstore\_to\_array ( hstore ) → text\[]
Extracts an hstore's keys and values as an array of alternating keys and values.
hstore\_to\_array('a=>1,b=>2') → {a,1,b,2}
|
| hstore\_to\_matrix ( hstore ) → text\[]
Extracts an hstore's keys and values as a two-dimensional array.
hstore\_to\_matrix('a=>1,b=>2') → {{a,1},{b,2}}
|
| hstore\_to\_json ( hstore ) → json
Converts an hstore to a json value, converting all non-null values to JSON strings.
This function is used implicitly when an hstore value is cast to json.
hstore\_to\_json('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
|
| hstore\_to\_jsonb ( hstore ) → jsonb
Converts an hstore to a jsonb value, converting all non-null values to JSON strings.
This function is used implicitly when an hstore value is cast to jsonb.
hstore\_to\_jsonb('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}
|
| hstore\_to\_json\_loose ( hstore ) → json
Converts an hstore to a json value, but attempts to distinguish numerical and Boolean values so they are unquoted in the JSON.
hstore\_to\_json\_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}
|
| hstore\_to\_jsonb\_loose ( hstore ) → jsonb
Converts an hstore to a jsonb value, but attempts to distinguish numerical and Boolean values so they are unquoted in the JSON.
hstore\_to\_jsonb\_loose('"a key"=>1, b=>t, c=>null, d=>12345, e=>012345, f=>1.234, g=>2.345e+4') → {"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}
|
| slice ( hstore, text\[] ) → hstore
Extracts a subset of an hstore containing only the specified keys.
slice('a=>1,b=>2,c=>3'::hstore, ARRAY\['b','c','x']) → "b"=>"2", "c"=>"3"
|
| each ( hstore ) → setof record ( key text, value text )
Extracts an hstore's keys and values as a set of records.
select \* from each('a=>1,b=>2') →
|
| exist ( hstore, text ) → boolean
Does hstore contain key?
exist('a=>1', 'a') → t
|
| defined ( hstore, text ) → boolean
Does hstore contain a non-NULL value for key?
defined('a=>NULL', 'a') → f
|
| delete ( hstore, text ) → hstore
Deletes pair with matching key.
delete('a=>1,b=>2', 'b') → "a"=>"1"
|
| delete ( hstore, text\[] ) → hstore
Deletes pairs with matching keys.
delete('a=>1,b=>2,c=>3', ARRAY\['a','b']) → "c"=>"3"
|
| delete ( hstore, hstore ) → hstore
Deletes pairs matching those in the second argument.
delete('a=>1,b=>2', 'a=>4,b=>2'::hstore) → "a"=>"1"
|
| populate\_record ( anyelement, hstore ) → anyelement
Replaces fields in the left operand (which must be a composite type) with matching values from hstore.
populate\_record(ROW(1,2), 'f1=>42'::hstore) → (42,2)
|
In addition to these operators and functions, values of the `hstore` type can be subscripted, allowing them to act like associative arrays. Only a single subscript of type `text` can be specified; it is interpreted as a key and the corresponding value is fetched or stored. For example,
```
CREATE TABLE mytable (h hstore);
INSERT INTO mytable VALUES ('a=>b, c=>d');
SELECT h['a'] FROM mytable;
h
---
b
(1 row)
UPDATE mytable SET h['c'] = 'new';
SELECT h FROM mytable;
h
----------------------
"a"=>"b", "c"=>"new"
(1 row)
```
A subscripted fetch returns `NULL` if the subscript is `NULL` or that key does not exist in the `hstore`. (Thus, a subscripted fetch is not greatly different from the `->` operator.) A subscripted update fails if the subscript is `NULL`; otherwise, it replaces the value for that key, adding an entry to the `hstore` if the key does not already exist.
## F.16.3. Indexes
`hstore` has GiST and GIN index support for the `@>`, `?`, `?&` and `?|` operators. For example:
```
CREATE INDEX hidx ON testhstore USING GIST (h);
CREATE INDEX hidx ON testhstore USING GIN (h);
```
`gist_hstore_ops` GiST opclass approximates a set of key/value pairs as a bitmap signature. Its optional integer parameter `siglen` determines the signature length in bytes. The default length is 16 bytes. Valid values of signature length are between 1 and 2024 bytes. Longer signatures lead to a more precise search (scanning a smaller fraction of the index and fewer heap pages), at the cost of a larger index.
Example of creating such an index with a signature length of 32 bytes:
```
CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));
```
`hstore` also supports `btree` or `hash` indexes for the `=` operator. This allows `hstore` columns to be declared `UNIQUE`, or to be used in `GROUP BY`, `ORDER BY` or `DISTINCT` expressions. The sort ordering for `hstore` values is not particularly useful, but these indexes may be useful for equivalence lookups. Create indexes for `=` comparisons as follows:
```
CREATE INDEX hidx ON testhstore USING BTREE (h);
CREATE INDEX hidx ON testhstore USING HASH (h);
```
## F.16.4. Examples
Add a key, or update an existing key with a new value:
```
UPDATE tab SET h['c'] = '3';
```
Another way to do the same thing is:
```
UPDATE tab SET h = h || hstore('c', '3');
```
If multiple keys are to be added or changed in one operation, the concatenation approach is more efficient than subscripting:
```
UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']);
```
Delete a key:
```
UPDATE tab SET h = delete(h, 'k1');
```
Convert a `record` to an `hstore`:
```
CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');
SELECT hstore(t) FROM test AS t;
hstore
---------------------------------------------
"col1"=>"123", "col2"=>"foo", "col3"=>"bar"
(1 row)
```
Convert an `hstore` to a predefined `record` type:
```
CREATE TABLE test (col1 integer, col2 text, col3 text);
SELECT * FROM populate_record(null::test,
'"col1"=>"456", "col2"=>"zzz"');
col1 | col2 | col3
------+------+------
456 | zzz |
(1 row)
```
Modify an existing record using the values from an `hstore`:
```
CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');
SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;
col1 | col2 | col3
------+------+------
123 | foo | baz
(1 row)
```
## F.16.5. Statistics
The `hstore` type, because of its intrinsic liberality, could contain a lot of different keys. Checking for valid keys is the task of the application. The following examples demonstrate several techniques for checking keys and obtaining statistics.
Simple example:
```
SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');
```
Using a table:
```
CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value FROM testhstore;
```
Online statistics:
```
SELECT key, count(*) FROM
(SELECT (each(h)).key FROM testhstore) AS stat
GROUP BY key
ORDER BY count DESC, key;
key | count
-----------+-------
line | 883
query | 207
pos | 203
node | 202
space | 197
status | 195
public | 194
title | 190
org | 189
...................
```
## F.16.6. Compatibility
As of PostgreSQL 9.0, `hstore` uses a different internal representation than previous versions. This presents no obstacle for dump/restore upgrades since the text representation (used in the dump) is unchanged.
In the event of a binary upgrade, upward compatibility is maintained by having the new code recognize old-format data. This will entail a slight performance penalty when processing data that has not yet been modified by the new code. It is possible to force an upgrade of all values in a table column by doing an `UPDATE` statement as follows:
```
UPDATE tablename SET hstorecol = hstorecol || '';
```
Another way to do it is:
```
ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
```
The `ALTER TABLE` method requires an `ACCESS EXCLUSIVE` lock on the table, but does not result in bloating the table with old row versions.
## F.16.7. Transforms
Additional extensions are available that implement transforms for the `hstore` type for the languages PL/Perl and PL/Python. The extensions for PL/Perl are called `hstore_plperl` and `hstore_plperlu`, for trusted and untrusted PL/Perl. If you install these transforms and specify them when creating a function, `hstore` values are mapped to Perl hashes. The extensions for PL/Python are called `hstore_plpythonu`, `hstore_plpython2u`, and `hstore_plpython3u` (see [Section 46.1](https://www.postgresql.org/docs/14/plpython-python23.html) for the PL/Python naming convention). If you use them, `hstore` values are mapped to Python dictionaries.
#### Caution
It is strongly recommended that the transform extensions be installed in the same schema as `hstore`. Otherwise there are installation-time security hazards if a transform extension's schema contains objects defined by a hostile user.
## F.16.8. Authors
Oleg Bartunov `<`[`oleg@sai.msu.su`](mailto:oleg@sai.msu.su)`>`, Moscow, Moscow University, Russia
Teodor Sigaev `<`[`teodor@sigaev.ru`](mailto:teodor@sigaev.ru)`>`, Moscow, Delta-Soft Ltd., Russia
Additional enhancements by Andrew Gierth `<`[`andrew@tao11.riddles.org.uk`](mailto:andrew@tao11.riddles.org.uk)`>`, United Kingdom