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 RepresentationThe text representation of an hstore, used for input and output, includes zero or more key => value pairs separated by commas. Some examples:
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:
A value (but not a key) can be an SQL NULL. For example:
The NULL keyword is case-insensitive. Double-quote the NULL to treat it as the ordinary string “NULL”.
On output, double quotes always surround keys and values, even when it's not strictly necessary.
hstore Operators and Functionshstore Operatorshstore FunctionsIn addition to these operators and functions, values of the 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,
A subscripted fetch returns 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.
Example of creating such an index with a signature length of 32 bytes:
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:
Add a key, or update an existing key with a new value:
Another way to do the same thing is:
If multiple keys are to be added or changed in one operation, the concatenation approach is more efficient than subscripting:
Delete a key:
Convert a record to an hstore:
Convert an hstore to a predefined record type:
Modify an existing record using the values from an hstore:
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:
Using a table:
Online 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:
Another way to do it is:
The ALTER TABLE method requires an ACCESS EXCLUSIVE lock on the table, but does not result in bloating the table with old row versions.
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.