hstoredata 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.
CREATEprivilege on the current database.
hstore, used for input and output, includes zero or more
valuepairs separated by commas. Some examples:
=>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.
hstoreis unique. If you declare an
hstorewith duplicate keys, only one will be stored in the
hstoreand there is no guarantee as to which will be kept:
NULL. For example:
NULLkeyword is case-insensitive. Double-quote the
NULLto treat it as the ordinary string “NULL”.
hstoretext format, when used for input, applies before any required quoting or escaping. If you are passing an
hstoreliteral 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_stringsconfiguration parameter) backslash characters need to be escaped correctly. See Section 126.96.36.199 for more on the handling of string constants.
hstoreOperators and Functions
hstoretype can be subscripted, allowing them to act like associative arrays. Only a single subscript of type
textcan be specified; it is interpreted as a key and the corresponding value is fetched or stored. For example,
NULLif the subscript is
NULLor 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
hstoreif the key does not already exist.
hstorehas GiST and GIN index support for the
?|operators. For example:
gist_hstore_opsGiST opclass approximates a set of key/value pairs as a bitmap signature. Its optional integer parameter
siglendetermines 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.
hashindexes for the
=operator. This allows
hstorecolumns to be declared
UNIQUE, or to be used in
DISTINCTexpressions. The sort ordering for
hstorevalues is not particularly useful, but these indexes may be useful for equivalence lookups. Create indexes for
=comparisons as follows:
hstoreto a predefined
hstoretype, 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.
hstoreuses 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.
UPDATEstatement as follows:
ALTER TABLEmethod requires an
ACCESS EXCLUSIVElock on the table, but does not result in bloating the table with old row versions.
hstoretype 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,
hstorevalues are mapped to Perl hashes. The extensions for PL/Python are called
hstore_plpython3u(see Section 46.1 for the PL/Python naming convention). If you use them,
hstorevalues are mapped to Python dictionaries.
hstore. Otherwise there are installation-time security hazards if a transform extension's schema contains objects defined by a hostile user.