hstore
mdx: format: md
This module implements the hstore data type for storing key-value pairs within a single value. This is useful in many scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. Both keys and values are simple text strings.
This module is considered "trusted", meaning it can be installed by non-superusers who have CREATE privilege on the current database.
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 key-value pairs is not significant (and is not reproduced on output). Whitespace between key-value pairs and around the => sign is ignored. Keys and values enclosed in double quotes can include whitespace, commas, =, or >. 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 which one will be retained:
test=## create extension hstore;
test=## SELECT 'a=>1,a=>2'::hstore;
hstore
----------
"a"=>"1"
(1 row)
A value (but not a key) can be SQL NULL. For example:
key => NULL
The NULL keyword is case-insensitive. Placing NULL inside double quotes treats it as the ordinary string "NULL".
| Note: Keep in mind that when the hstore text format is used for input, it is applied before any necessary quoting or escaping. If you pass an hstore literal via a parameter, no additional processing is needed. But if you pass it as a quoted literal constant, any single quote characters and (depending on the standard_conforming_strings configuration parameter) backslash characters need to be properly escaped. |
|---|
On output, double quotes always surround keys and values, even when this is not strictly necessary.
2. hstore Operators and Functions
The operators provided by the hstore module are shown in Table C.7, and the functions in Table C.8.
Table C.7. hstore Operators
| Operator/Description/Example |
|---|
| hstore -> text → text Returns the value associated with the given key, or NULL if it does not exist. 'a=>x, b=>y'::hstore -> 'a' → x |
| hstore -> text[] → text[] Returns the values associated with the given key(s), or NULL if not found. 'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a'] → {"z","x"} |
| hstore |
| hstore ? text → boolean Does hstore contain the 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 |
| store ? |
| hstore @> hstore → boolean Does the left operand contain the right operand? 'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1' →t |
| hstore - text → hstore Deletes the key from the left operand. 'a=>1, b=>2, c=>3'::hstore - 'b'::text → "a"=>"1", "c"=>"3" |
| hstore - text[] → hstore Deletes the key(s) from the left operand. 'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b'] → "c"=>"3" |
| hstore - hstore → hstore Deletes pairs from the left operand that match those 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 the 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}} |
| Note: The operators @> and <@ were formerly called @ and ~, respectively. These names are still available but are deprecated and will eventually be removed. The old names followed a convention opposite to that of the original core geometric data types! |
|---|
Table C.8. hstore Functions
| Function/Description/Example |
|---|
| 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 can be 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 Creates a single-item hstore. hstore('a', 'b') → "a"=>"b" |
| akeys ( hstore ) → text[] Extracts the keys of an hstore as an array. akeys('a=>1,b=>2') → {a,b} |
| skeys ( hstore ) → setof text Extracts the keys of an hstore as a set. skeys('a=>1,b=>2') → a b |
| avals ( hstore ) → text[] Extracts the values of an hstore as an array. avals('a=>1,b=>2') → {1,2} |
| svals ( hstore ) → setof text Extracts the values of an hstore as a set. svals('a=>1,b=>2') → 1 2 |
| hstore_to_array ( hstore ) → text[] Extracts the keys and values of an hstore as an alternating key/value array. hstore_to_array('a=>1,b=>2') → {a,1,b,2} |
| hstore_to_matrix ( hstore ) → text[] Extracts the keys and values of an hstore as a two-dimensional array. hstore_to_matrix('a=>1,b=>2') → {{a,1},{b,2}} |
| hstore_to_json ( hstore ) → json Converts hstore to a json value, converting all non-null values to JSON strings. This function is used implicitly when hstore values are 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 hstore to a jsonb value, converting all non-null values to JSON strings. This function is used implicitly when hstore values are 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 hstore to a json value, but attempts to distinguish numeric and boolean values so they are unquoted in 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 hstore to a jsonb value, but attempts to distinguish numeric and boolean values so they are unquoted in 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 the keys and values of an hstore as a set of records. |
| exist ( hstore, text ) → boolean Does hstore contain the key? exist('a=>1', 'a') → t |
| defined ( hstore, text ) → boolean Does hstore contain a non-NULL value for the key? defined('a=>NULL', 'a') → f |
| delete ( hstore, text ) → hstore Deletes pairs 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 the hstore. populate_record(ROW(1,2), 'f1=>42'::hstore) → (42,2) |
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);
The gist_hstore_ops GiST operator class 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 for the signature length are between 1 and 2024 bytes. Longer signatures result in more precise searches (scanning a smaller fraction of the index and fewer heap pages) at the cost of a larger index.
An example of creating one with a 32-byte signature length:
CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));
hstore also supports btree and hash indexes for the = operator. This allows hstore columns to be declared UNIQUE or used in GROUP BY, ORDER BY, or DISTINCT expressions. The sort order of hstore values is not particularly useful, but these indexes may be useful for equality lookups. Create the following indexes for equality comparisons:
CREATE INDEX hidx ON testhstore USING BTREE (h);
CREATE INDEX hidx ON testhstore USING HASH (h);
4. Examples
Add a key, or update an existing key with a new value:
UPDATE tab SET h = h || hstore('c', '3');
Delete a key:
UPDATE tab SET h = delete(h, 'k1');
Convert a record to an hstore:
test=## CREATE TABLE test (col1 integer, col2 text, col3 text);
CREATE TABLE
test=## INSERT INTO test VALUES (123, 'foo', 'bar');
INSERT 0 1
test=## SELECT hstore(t) FROM test AS t;
hstore
---------------------------------------------
"col1"=>"123", "col2"=>"foo", "col3"=>"bar"
(1 row)
Convert an hstore to a predefined record type:
test=## CREATE TABLE test (col1 integer, col2 text, col3 text);
CREATE TABLE
test=## SELECT * FROM populate_record(null::test,
test(## '"col1"=>"456", "col2"=>"zzz"');
col1 | col2 | col3
------+------+------
456 | zzz |
(1 row)
Modify an existing record using values from an hstore:
test=## CREATE TABLE test (col1 integer, col2 text, col3 text);
CREATE TABLE
test=## INSERT INTO test VALUES (123, 'foo', 'bar');
INSERT 0 1
test=## SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;
col1 | col2 | col3
------+------+------
123 | foo | baz
(1 row)
5. Statistics
Due to the inherently permissive nature of the hstore type, it can contain many different keys. Validating keys is the responsibility of the application. The following example demonstrates some techniques for checking keys and obtaining statistics.
Simple example:
test=## SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');
key | value
-----+-------
| 1
b |
aaa | bq
(3 rows)
Using a table:
SELECT (each(h)).key, (each(h)).value INTO stat 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 ...................
6. Compatibility
Using hstore does not pose an obstacle for dump/restore upgrades, since the text representation (used for dumps) has not changed.
In a binary upgrade, upward compatibility is maintained by having the new code recognize old format data. This incurs a slight performance penalty when processing data that has not yet been modified by the new code. You can force an upgrade of all values in a table by executing one of the following UPDATE statements:
UPDATE tablename SET hstorecol = hstorecol || '';
An alternative method:
ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || ''; The ALTER TABLE method requires an exclusive lock on the table, but does not cause the table to bloat with old row versions.
7. Transforms
There are additional extensions that implement transforms for the hstore type for the PL/Perl and PL/Python languages. The extensions for PL/Perl are called hstore_plperl and hstore_plperlu, for trusted and untrusted PL/Perl respectively. If these transforms are installed and specified when creating functions, hstore values are mapped to Perl hashes. The extensions for PL/Python are hstore_plpythonu, hstore_plpython2u, and hstore_plpython3u. If they are used, hstore values are mapped to Python dictionaries.
| Caution: It is strongly recommended to install the transform extensions in the same schema as hstore. Otherwise, if the transform extension's schema contains objects defined by hostile users, there are security concerns during installation. |
|---|