This module implements the
hstore data type for storing sets of key/value pairs within a single Postgres Pro 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.
hstore External Representation
The text representation of an
hstore, used for input and output, includes zero or more
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. 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
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 18.104.22.168 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
|get value for key (|
|get values for keys (|
|does left operand contain right?|
|is left operand contained in right?|
|delete key from left operand|
|delete keys from left operand|
|delete matching pairs from left operand|
|replace fields in ||see Examples section|
Prior to PostgreSQL 8.2, the containment operators
<@ were called
~, 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!
hstore_to_json is used when an
hstore value is cast to
hstore_to_jsonb is used when an
hstore value is cast to
populate_record is actually declared with
record, as its first argument, but it will reject non-record types with a run-time error.
hstore has GiST and GIN index support for the
?| operators. For example:
CREATE INDEX hidx ON testhstore USING GIST (h); CREATE INDEX hidx ON testhstore USING GIN (h);
hstore also supports
hash indexes for the
= operator. This allows
hstore columns to be declared
UNIQUE, or to be used in
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');
record to an
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)
hstore to a predefined
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
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)
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.
SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');
Using a table:
SELECT (each(h)).key, (each(h)).value INTO stat FROM testhstore;
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 || '';
ALTER TABLE method requires an 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_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_plpython3u (see Section 45.1 for the PL/Python naming convention). If you use them,
hstore values are mapped to Python dictionaries.