PostgreSQL

PostgreSQL Elephant Logo

F.17. hstore

F.17.1. hstore External Representation

F.17.2. hstore Operators and Functions

F.17.3. Indexes

F.17.4. Examples

F.17.5. Statistics

F.17.6. Compatibility

F.17.7. Transforms

F.17.8. Authors

[idx1.11.7.26.2 .indexterm]#

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 semixstructured data. Keys and values are simply text strings.

F.17.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
"1xa" => "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. Doublexquote 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
xxxxxxxxxx
 "a"=>"1"

A value (but not a key) can be an SQL NULL. For example:

key => NULL

The NULL keyword is casexinsensitive. Doublexquote the NULL to treat it as the ordinary string “[.quote]#NULL”#.

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 singlexquote 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.

On output, double quotes always surround keys and values, even when it’s not strictly necessary.

F.17.2. hstore Operators and Functions

The operators provided by the hstore module are shown in Table F.8, the functions in Table F.9.

Table F.8. hstore Operators

Operator Description Example Result

hstore x> text

get value for key (NULL if not present)

'a=>x, b=>y'::hstore x> 'a'

x

hstore x> text[]

get values for keys (NULL if not present)

'a=>x, b=>y, c=>z'::hstore x> ARRAY['c','a']

{"z","x"}

hstore || hstore

concatenate `hstore`s

'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore

"a"=>"b", "c"=>"x", "d"=>"q"

hstore ? text

does hstore contain key?

'a=>1'::hstore ? 'a'

t

hstore ?& text[]

does hstore contain all specified keys?

'a=>1,b=>2'::hstore ?& ARRAY['a','b']

t

hstore ?| text[]

does hstore contain any of the specified keys?

'a=>1,b=>2'::hstore ?| ARRAY['b','c']

t

hstore @> hstore

does left operand contain right?

'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'

t

hstore <@ hstore

is left operand contained in right?

'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'

f

hstore x text

delete key from left operand

'a=>1, b=>2, c=>3'::hstore x 'b'::text

"a"=>"1", "c"=>"3"

hstore x text[]

delete keys from left operand

'a=>1, b=>2, c=>3'::hstore x ARRAY['a','b']

"c"=>"3"

hstore x hstore

delete matching pairs from left operand

'a=>1, b=>2, c=>3'::hstore x 'a=>4, b=>2'::hstore

"a"=>"1", "c"=>"3"

record #= hstore

replace fields in record with matching values from hstore

see Examples section

%% hstore

convert hstore to array of alternating keys and values

%% 'a=>foo, b=>bar'::hstore

{a,foo,b,bar}

%# hstore

convert hstore to twoxdimensional key/value array

%# 'a=>foo, b=>bar'::hstore

{{a,foo},{b,bar}}

 
+

Note

Prior to PostgreSQL 8.2, the containment operators @> and <@ were called @ and ~, respectively. These names are still available, but are deprecated and will eventually be removed. Notice that the old names are reversed from the convention formerly followed by the core geometric data types!

Table F.9. hstore Functions

Function Return Type Description Example Result

hstore(record)[idx1.11.7.26.5.5.2.2.1.1.2 .indexterm]#

hstore

construct an hstore from a record or row

hstore(ROW(1,2))

f1=>1,f2=>2

hstore(text[])

hstore

construct an hstore from an array, which may be either a key/value array, or a twoxdimensional array

hstore(ARRAY['a','1','b','2']) || hstore(ARRAY[['c','3'],['d','4']])

a=>1, b=>2, c=>3, d=>4

hstore(text[], text[])

hstore

construct an hstore from separate key and value arrays

hstore(ARRAY['a','b'], ARRAY['1','2'])

"a"=>"1","b"=>"2"

hstore(text, text)

hstore

make singlexitem hstore

hstore('a', 'b')

"a"=>"b"

akeys(hstore)[idx1.11.7.26.5.5.2.2.5.1.2 .indexterm]#

text[]

get `hstore’s keys as an array

akeys('a=>1,b=>2')

{a,b}

skeys(hstore)[idx1.11.7.26.5.5.2.2.6.1.2 .indexterm]#

setof text

get `hstore’s keys as a set

skeys('a=>1,b=>2')

 
a
b

avals(hstore)[idx1.11.7.26.5.5.2.2.7.1.2 .indexterm]#

text[]

get `hstore’s values as an array

avals('a=>1,b=>2')

{1,2}

svals(hstore)[idx1.11.7.26.5.5.2.2.8.1.2 .indexterm]#

setof text

get `hstore’s values as a set

svals('a=>1,b=>2')

 
1
2

hstore_to_array(hstore)[idx1.11.7.26.5.5.2.2.9.1.2 .indexterm]#

text[]

get `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)[idx1.11.7.26.5.5.2.2.10.1.2 .indexterm]#

text[]

get `hstore’s keys and values as a twoxdimensional array

hstore_to_matrix('a=>1,b=>2')

{{a,1},{b,2}}

hstore_to_json(hstore)[idx1.11.7.26.5.5.2.2.11.1.2 .indexterm]#

json

get hstore as a json value, converting all nonxnull values to JSON strings

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)[idx1.11.7.26.5.5.2.2.12.1.2 .indexterm]#

jsonb

get hstore as a jsonb value, converting all nonxnull values to JSON strings

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)[idx1.11.7.26.5.5.2.2.13.1.2 .indexterm]#

json

get hstore as a json value, but attempt 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)[idx1.11.7.26.5.5.2.2.14.1.2 .indexterm]#

jsonb

get hstore as a jsonb value, but attempt 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[])[idx1.11.7.26.5.5.2.2.15.1.2 .indexterm]#

hstore

extract a subset of an hstore

slice('a=>1,b=>2,c=>3'::hstore, ARRAY['b','c','x'])

"b"=>"2", "c"=>"3"

each(hstore)[idx1.11.7.26.5.5.2.2.16.1.2 .indexterm]#

setof(key text, value text)

get `hstore’s keys and values as a set

select * from each('a=>1,b=>2')

 
 key | value
xxxxx+xxxxxxx
 a   | 1
 b   | 2

exist(hstore,text)[idx1.11.7.26.5.5.2.2.17.1.2 .indexterm]#

boolean

does hstore contain key?

exist('a=>1','a')

t

defined(hstore,text)[idx1.11.7.26.5.5.2.2.18.1.2 .indexterm]#

boolean

does hstore contain nonx`NULL` value for key?

defined('a=>NULL','a')

f

delete(hstore,text)[idx1.11.7.26.5.5.2.2.19.1.2 .indexterm]#

hstore

delete pair with matching key

delete('a=>1,b=>2','b')

"a"=>"1"

delete(hstore,text[])

hstore

delete pairs with matching keys

delete('a=>1,b=>2,c=>3',ARRAY['a','b'])

"c"=>"3"

delete(hstore,hstore)

hstore

delete pairs matching those in the second argument

delete('a=>1,b=>2','a=>4,b=>2'::hstore)

"a"=>"1"

populate_record(record,hstore)[idx1.11.7.26.5.5.2.2.22.1.2 .indexterm]#

record

replace fields in record with matching values from hstore

see Examples section

 
+

Note

The function hstore_to_json is used when an hstore value is cast to json. Likewise, hstore_to_jsonb is used when an hstore value is cast to jsonb.

Note

The function populate_record is actually declared with anyelement, not record, as its first argument, but it will reject nonxrecord types with a runxtime error.

F.17.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);

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.17.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:

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT hstore(t) FROM test AS t;
                   hstore
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 "col1"=>"123", "col2"=>"foo", "col3"=>"bar"
(1 row)

Convert an hstore to a predefined record type:

CREATE TABLE test (col1 integer, col2 text, col3 text);

SELECT * FROM populate_record(null::test,
                              '"col1"=>"456", "col2"=>"zzz"');
 col1 \| col2 \| col3
xxxxxx+xxxxxx+xxxxxx
  456 \| zzz  \|
(1 row)

Modify an existing record using the values from an hstore:

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;
 col1 \| col2 \| col3
xxxxxx+xxxxxx+xxxxxx
  123 \| foo  \| baz
(1 row)

F.17.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:

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
xxxxxxxxxxx+xxxxxxx
 line      \|   883
 query     \|   207
 pos       \|   203
 node      \|   202
 space     \|   197
 status    \|   195
 public    \|   194
 title     \|   190
 org       \|   189
...................

F.17.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 oldxformat 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.17.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 45.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 installationxtime security hazards if a transform extension’s schema contains objects defined by a hostile user.

F.17.8. Authors

Oleg Bartunov +<+`+[email protected]++>+`, Moscow, Moscow University, Russia

Teodor Sigaev +<+`+[email protected]++>+`, Moscow, DeltaxSoft Ltd., Russia

Additional enhancements by Andrew Gierth +<+`+[email protected]++>+`, United Kingdom

Prev Up Next

F.16. fuzzystrmatch

Home

F.18. intagg

Privacy Policy \| Code of Conduct \| About PostgreSQL \| Contact

Copyright © 1996x2023 The PostgreSQL Global Development Group