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.

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
----------
"a"=>"1"

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

key => NULL

The NULL keyword is
case-insensitive. Double-quote the NULL to treat it as the ordinary string
"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 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.

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

The operators provided by the hstore module are shown in Table F-6, the functions in
Table F-7.

Table F-6. hstore
Operators

Operator

Description

Example

Result

hstore->text

get value for key (NULL if
not present)

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

x

hstore->text[]

get values for keys (NULL
if not present)

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

{"z","x"}

hstore||hstore

concatenate hstores

'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-text

delete key from left operand

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

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

hstore-text[]

delete keys from left operand

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

"c"=>"3"

hstore-hstore

delete matching pairs from left operand

'a=>1, b=>2,
c=>3'::hstore - '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
two-dimensional 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-7. hstore
Functions

Function

Return Type

Description

Example

Result

hstore(record)

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
two-dimensional 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 single-item hstore

hstore('a', 'b')

"a"=>"b"

akeys(hstore)

text[]

get hstore's keys as an
array

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

{a,b}

skeys(hstore)

setof text

get hstore's keys as a
set

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

a
b

avals(hstore)

text[]

get hstore's values as an
array

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

{1,2}

svals(hstore)

setof text

get hstore's values as a
set

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

1
2

hstore_to_array(hstore)

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)

text[]

get hstore's keys and values
as a two-dimensional array

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

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

slice(hstore,
text[])

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)

setof(key text, value
text)

get hstore's keys and values
as a set

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

key | value
-----+-------
a | 1
b | 2

exist(hstore,text)

boolean

does hstore contain key?

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

t

defined(hstore,text)

boolean

does hstore contain
non-NULL value for key?

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

f

delete(hstore,text)

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)

record

replace fields in record with
matching values from hstore

see Examples section

Note: The function populate_record is actually declared with
anyelement, not record, as its first argument, but it will
reject non-record types with a run-time error.

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:

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.

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: