hstore.md

## 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<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](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