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.CREATE
privilege on the current database.hstore
External Representationhstore
, used for input and output, includes zero or more key
=>
value
pairs separated by commas. Some examples:=>
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.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:NULL
. For example:NULL
keyword is case-insensitive. Double-quote the NULL
to treat it as the ordinary string “NULL”.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 for more on the handling of string constants.hstore
Operators and Functionshstore
Operatorshstore
->
text
→ text
NULL
if not present.'a=>x, b=>y'::hstore -> 'a'
→ x
hstore
->
text[]
→ text[]
NULL
if not present.'a=>x, b=>y, c=>z'::hstore -> ARRAY['c','a']
→ {"z","x"}
hstore
hstore
?
text
→ boolean
hstore
contain key?'a=>1'::hstore ? 'a'
→ t
hstore
?&
text[]
→ boolean
hstore
contain all the specified keys?'a=>1,b=>2'::hstore ?& ARRAY['a','b']
→ t
hstore
?
hstore
@>
hstore
→ boolean
'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'
→ t
hstore
<@
hstore
→ boolean
'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'
→ f
hstore
-
text
→ hstore
'a=>1, b=>2, c=>3'::hstore - 'b'::text
→ "a"=>"1", "c"=>"3"
hstore
-
text[]
→ hstore
'a=>1, b=>2, c=>3'::hstore - ARRAY['a','b']
→ "c"=>"3"
hstore
-
hstore
→ hstore
'a=>1, b=>2, c=>3'::hstore - 'a=>4, b=>2'::hstore
→ "a"=>"1", "c"=>"3"
anyelement
#=
hstore
→ anyelement
hstore
.ROW(1,3) #= 'f1=>11'::hstore
→ (11,3)
%%
hstore
→ text[]
hstore
to an array of alternating keys and values.%% 'a=>foo, b=>bar'::hstore
→ {a,foo,b,bar}
%#
hstore
→ text[]
hstore
to a two-dimensional key/value array.%# 'a=>foo, b=>bar'::hstore
→ {{a,foo},{b,bar}}
hstore
Functionshstore
( record
) → hstore
hstore
from a record or row.hstore(ROW(1,2))
→ "f1"=>"1", "f2"=>"2"
hstore
( text[]
) → hstore
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
hstore
from separate key and value arrays.hstore(ARRAY['a','b'], ARRAY['1','2'])
→ "a"=>"1", "b"=>"2"
hstore
( text
, text
) → hstore
hstore
.hstore('a', 'b')
→ "a"=>"b"
akeys
( hstore
) → text[]
hstore
's keys as an array.akeys('a=>1,b=>2')
→ {a,b}
skeys
( hstore
) → setof text
hstore
's keys as a set.skeys('a=>1,b=>2')
→avals
( hstore
) → text[]
hstore
's values as an array.avals('a=>1,b=>2')
→ {1,2}
svals
( hstore
) → setof text
hstore
's values as a set.svals('a=>1,b=>2')
→hstore_to_array
( hstore
) → text[]
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[]
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
hstore
to a json
value, converting all non-null values to JSON strings.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
hstore
to a jsonb
value, converting all non-null values to JSON strings.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
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
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
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
)hstore
's keys and values as a set of records.select * from each('a=>1,b=>2')
→exist
( hstore
, text
) → boolean
hstore
contain key?exist('a=>1', 'a')
→ t
defined
( hstore
, text
) → boolean
hstore
contain a non-NULL
value for key?defined('a=>NULL', 'a')
→ f
delete
( hstore
, text
) → hstore
delete('a=>1,b=>2', 'b')
→ "a"=>"1"
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,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.hstore
has GiST and GIN index support for the @>
, ?
, ?&
and ?|
operators. For example: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.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:record
to an hstore
:hstore
to a predefined record
type:hstore
: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.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.UPDATE
statement as follows:ALTER TABLE
method requires an ACCESS EXCLUSIVE
lock on the table, but does not result in bloating the table with old row versions.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.hstore
. Otherwise there are installation-time security hazards if a transform extension's schema contains objects defined by a hostile user.