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.
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 ---------- "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”.
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.
hstore
Operators and Functions
The operators provided by the hstore
module are
shown in Table F.7, the functions
in Table F.8.
Table F.7. hstore
Operators
Operator Description Example(s) |
---|
Returns value associated with given key, or
|
Returns values associated with given keys, or
|
Concatenates two
|
Does
|
Does
|
Does
|
Does left operand contain right?
|
Is left operand contained in right?
|
Deletes key from left operand.
|
Deletes keys from left operand.
|
Deletes pairs from left operand that match pairs in the right operand.
|
Replaces fields in the left operand (which must be a composite type)
with matching values from
|
Converts
|
Converts
|
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.8. hstore
Functions
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);
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 --------------------------------------------- "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 ------+------+------ 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 ------+------+------ 123 | foo | baz (1 row)
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 -----------+------- line | 883 query | 207 pos | 203 node | 202 space | 197 status | 195 public | 194 title | 190 org | 189 ...................
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.
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 for the PL/Python naming
convention). If you use them, hstore
values are mapped to
Python dictionaries.
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.
Oleg Bartunov <oleg@sai.msu.su>
, Moscow, Moscow University, Russia
Teodor Sigaev <teodor@sigaev.ru>
, Moscow, Delta-Soft Ltd., Russia
Additional enhancements by Andrew Gierth <andrew@tao11.riddles.org.uk>
,
United Kingdom