## F.16. hstore
[F.16.1. `hstore` External Representation](hstore.html#id-1.11.7.25.5)[F.16.2. `hstore` Operators and Functions](hstore.html#id-1.11.7.25.6)[F.16.3. Indexes](hstore.html#id-1.11.7.25.7)[F.16.4. Examples](hstore.html#id-1.11.7.25.8)[F.16.5. Statistics](hstore.html#id-1.11.7.25.9)[F.16.6. Compatibility](hstore.html#id-1.11.7.25.10)[F.16.7. Transforms](hstore.html#id-1.11.7.25.11)[F.16.8. Authors](hstore.html#id-1.11.7.25.12)
[]()
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
### 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](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](hstore.html#HSTORE-OP-TABLE), the functions in [Table F.8](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` → `hstore`
Concatenates two `hstore`s.
`'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore` → `"a"=>"b", "c"=>"x", "d"=>"q"` |
| `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` `?|` `text[]` → `boolean`
Does `hstore` contain any of the specified keys?
`'a=>1,b=>2'::hstore ?| ARRAY['b','c']` → `t` |
| `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')` → ``
```
a
b
``` |
| []() `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')` → ``
```
1
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')` → ``
```
key | value
-----+-------
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
### 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
### 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
### 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](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