# F.16. hstore

F.16.1. hstore External RepresentationF.16.2. hstore Operators and FunctionsF.16.3. IndexesF.16.4. ExamplesF.16.5. StatisticsF.16.6. CompatibilityF.16.7. TransformsF.16.8. Authors

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<br/><br/> Description<br/><br/> Example(s)                                                                              |
|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|                            `hstore` `->` `text` → `text`<br/><br/> Returns value associated with given key, or `NULL` if not present.<br/><br/>`'a=>x, b=>y'::hstore -> 'a'` → `x`                            |
|            `hstore` `->` `text[]` → `text[]`<br/><br/> Returns values associated with given keys, or `NULL` if not present.<br/><br/>`'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a']` → `{"z","x"}`             |
|                       `hstore` `||` `hstore` → `hstore`<br/><br/> Concatenates two `hstore`s.<br/><br/>`'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore` → `"a"=>"b", "c"=>"x", "d"=>"q"`                        |
|                                                  `hstore` `?` `text` → `boolean`<br/><br/> Does `hstore` contain key?<br/><br/>`'a=>1'::hstore ? 'a'` → `t`                                                   |
|                               `hstore` `?&` `text[]` → `boolean`<br/><br/> Does `hstore` contain all the specified keys?<br/><br/>`'a=>1,b=>2'::hstore ?& ARRAY['a','b']` → `t`                               |
|                             `hstore` `?|` `text[]` → `boolean`<br/><br/> Does `hstore` contain any of the specified keys?<br/><br/>`'a=>1,b=>2'::hstore ?| ARRAY['b','c']` → `t`                              |
|                                    `hstore` `@>` `hstore` → `boolean`<br/><br/> Does left operand contain right?<br/><br/>`'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'` → `t`                                     |
|                                   `hstore` `<@` `hstore` → `boolean`<br/><br/> Is left operand contained in right?<br/><br/>`'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'` → `f`                                   |
|                               `hstore` `-` `text` → `hstore`<br/><br/> Deletes key from left operand.<br/><br/>`'a=>1, b=>2, c=>3'::hstore - 'b'::text` → `"a"=>"1", "c"=>"3"`                                |
|                                `hstore` `-` `text[]` → `hstore`<br/><br/> Deletes keys from left operand.<br/><br/>`'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b']` → `"c"=>"3"`                                 |
|     `hstore` `-` `hstore` → `hstore`<br/><br/> Deletes pairs from left operand that match pairs in the right operand.<br/><br/>`'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore` → `"a"=>"1", "c"=>"3"`     |
|`anyelement` `#=` `hstore` → `anyelement`<br/><br/> Replaces fields in the left operand (which must be a composite type) with matching values from `hstore`.<br/><br/>`ROW(1,3) #= 'f1=>11'::hstore` → `(11,3)`|
|                           `%%` `hstore` → `text[]`<br/><br/> Converts `hstore` to an array of alternating keys and values.<br/><br/>`%% 'a=>foo, b=>bar'::hstore` → `{a,foo,b,bar}`                           |
|                            `%#` `hstore` → `text[]`<br/><br/> Converts `hstore` to a two-dimensional key/value array.<br/><br/>`%# 'a=>foo, b=>bar'::hstore` → `{{a,foo},{b,bar}}`                            |

**Table F.8. `hstore` Functions**

|                                                                                                                                                                                              Function<br/><br/> Description<br/><br/> Example(s)                                                                                                                                                                                             |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|                                                                                                                                              []() `hstore` ( `record` ) → `hstore`<br/><br/> Constructs an `hstore` from a record or row.<br/><br/>`hstore(ROW(1,2))` → `"f1"=>"1", "f2"=>"2"`                                                                                                                                               |
|                                                                          `hstore` ( `text[]` ) → `hstore`<br/><br/> Constructs an `hstore` from an array, which may be either a key/value array, or a two-dimensional array.<br/><br/>`hstore(ARRAY['a','1','b','2'])` → `"a"=>"1", "b"=>"2"`<br/><br/>`hstore(ARRAY[['c','3'],['d','4']])` → `"c"=>"3", "d"=>"4"`                                                                           |
|                                                                                                                           `hstore` ( `text[]`, `text[]` ) → `hstore`<br/><br/> Constructs an `hstore` from separate key and value arrays.<br/><br/>`hstore(ARRAY['a','b'], ARRAY['1','2'])` → `"a"=>"1", "b"=>"2"`                                                                                                                           |
|                                                                                                                                                           `hstore` ( `text`, `text` ) → `hstore`<br/><br/> Makes a single-item `hstore`.<br/><br/>`hstore('a', 'b')` → `"a"=>"b"`                                                                                                                                                            |
|                                                                                                                                                       []() `akeys` ( `hstore` ) → `text[]`<br/><br/> Extracts an `hstore`'s keys as an array.<br/><br/>`akeys('a=>1,b=>2')` → `{a,b}`                                                                                                                                                        |
|                                                                                                                                      []() `skeys` ( `hstore` ) → `setof text`<br/><br/> Extracts an `hstore`'s keys as a set.<br/><br/>`skeys('a=>1,b=>2')` → ``<br/><br/>```<br/>a<br/>b<br/><br/>```                                                                                                                                       |
|                                                                                                                                                      []() `avals` ( `hstore` ) → `text[]`<br/><br/> Extracts an `hstore`'s values as an array.<br/><br/>`avals('a=>1,b=>2')` → `{1,2}`                                                                                                                                                       |
|                                                                                                                                     []() `svals` ( `hstore` ) → `setof text`<br/><br/> Extracts an `hstore`'s values as a set.<br/><br/>`svals('a=>1,b=>2')` → ``<br/><br/>```<br/>1<br/>2<br/><br/>```                                                                                                                                      |
|                                                                                                                      []() `hstore_to_array` ( `hstore` ) → `text[]`<br/><br/> Extracts an `hstore`'s keys and values as an array of alternating keys and values.<br/><br/>`hstore_to_array('a=>1,b=>2')` → `{a,1,b,2}`                                                                                                                       |
|                                                                                                                           []() `hstore_to_matrix` ( `hstore` ) → `text[]`<br/><br/> Extracts an `hstore`'s keys and values as a two-dimensional array.<br/><br/>`hstore_to_matrix('a=>1,b=>2')` → `{{a,1},{b,2}}`                                                                                                                            |
|  []() `hstore_to_json` ( `hstore` ) → `json`<br/><br/> Converts an `hstore` to a `json` value, converting all non-null values to JSON strings.<br/><br/> This function is used implicitly when an `hstore` value is cast to `json`.<br/><br/>`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`<br/><br/> Converts an `hstore` to a `jsonb` value, converting all non-null values to JSON strings.<br/><br/> This function is used implicitly when an `hstore` value is cast to `jsonb`.<br/><br/>`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`<br/><br/> Converts an `hstore` to a `json` value, but attempts to distinguish numerical and Boolean values so they are unquoted in the JSON.<br/><br/>`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`<br/><br/> Converts an `hstore` to a `jsonb` value, but attempts to distinguish numerical and Boolean values so they are unquoted in the JSON.<br/><br/>`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`<br/><br/> Extracts a subset of an `hstore` containing only the specified keys.<br/><br/>`slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x'])` → `"b"=>"2", "c"=>"3"`                                                                                                              |
|                                                                         []() `each` ( `hstore` ) → `setof record` ( *`key`* `text`, *`value`* `text` )<br/><br/> Extracts an `hstore`'s keys and values as a set of records.<br/><br/>`select * from each('a=>1,b=>2')` → ``<br/><br/>```<br/> key | value<br/>-----+-------<br/> a   | 1<br/> b   | 2<br/><br/>```                                                                          |
|                                                                                                                                                            []() `exist` ( `hstore`, `text` ) → `boolean`<br/><br/> Does `hstore` contain key?<br/><br/>`exist('a=>1', 'a')` → `t`                                                                                                                                                            |
|                                                                                                                                             []() `defined` ( `hstore`, `text` ) → `boolean`<br/><br/> Does `hstore` contain a non-`NULL` value for key?<br/><br/>`defined('a=>NULL', 'a')` → `f`                                                                                                                                             |
|                                                                                                                                                   []() `delete` ( `hstore`, `text` ) → `hstore`<br/><br/> Deletes pair with matching key.<br/><br/>`delete('a=>1,b=>2', 'b')` → `"a"=>"1"`                                                                                                                                                   |
|                                                                                                                                           `delete` ( `hstore`, `text[]` ) → `hstore`<br/><br/> Deletes pairs with matching keys.<br/><br/>`delete('a=>1,b=>2,c=>3', ARRAY['a','b'])` → `"c"=>"3"`                                                                                                                                            |
|                                                                                                                                  `delete` ( `hstore`, `hstore` ) → `hstore`<br/><br/> Deletes pairs matching those in the second argument.<br/><br/>`delete('a=>1,b=>2', 'a=>4,b=>2'::hstore)` → `"a"=>"1"`                                                                                                                                  |
|                                                                                            []() `populate_record` ( `anyelement`, `hstore` ) → `anyelement`<br/><br/> Replaces fields in the left operand (which must be a composite type) with matching values from `hstore`.<br/><br/>`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 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