Movatterモバイル変換

F.23.1.hstore External Representation

F.23.2.hstore Operators and Functions

F.23.3. Indexes

F.23.4. Examples

F.23.5. Statistics

F.23.6. Compatibility

F.23.7. Transforms

F.23.8. Authors

The text representation of anhstore, used for input and output, includes zero or morekey=>value pairs separated by commas. Some examples:

k => vfoo => 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 anhstore is unique. If you declare anhstore with duplicate keys, only one will be stored in thehstore 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 SQLNULL. For example:

key => NULL

TheNULL keyword is case-insensitive. Double-quote theNULL to treat it as the ordinary string“NULL”.

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

The operators provided by thehstore module are shown inTable F.10, the functions inTable F.11.

ab

12

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

In addition to these operators and functions, values of thehstore type can be subscripted, allowing them to act like associative arrays. Only a single subscript of typetext can be specified; it is interpreted as a key and the corresponding value is fetched or stored. For example,

CREATE TABLE mytable (h hstore);INSERT INTO mytable VALUES ('a=>b, c=>d');SELECT h['a'] FROM mytable; h--- b(1 row)UPDATE mytable SET h['c'] = 'new';SELECT h FROM mytable;          h---------------------- "a"=>"b", "c"=>"new"(1 row)

A subscripted fetch returnsNULL if the subscript isNULL or that key does not exist in thehstore. (Thus, a subscripted fetch is not greatly different from the-> operator.) A subscripted update fails if the subscript isNULL; otherwise, it replaces the value for that key, adding an entry to thehstore if the key does not already exist.

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 parametersiglen 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 supportsbtree orhash indexes for the= operator. This allowshstore columns to be declaredUNIQUE, or to be used inGROUP BY,ORDER BY orDISTINCT expressions. The sort ordering forhstore 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['c'] = '3';

Another way to do the same thing is:

UPDATE tab SET h = h || hstore('c', '3');

If multiple keys are to be added or changed in one operation, the concatenation approach is more efficient than subscripting:

UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']);

Delete a key:

UPDATE tab SET h = delete(h, 'k1');

Convert arecord to anhstore:

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 anhstore to a predefinedrecord 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 anhstore:

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)

Thehstore 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:

CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value 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 anUPDATE statement as follows:

UPDATE tablename SET hstorecol = hstorecol || '';

Another way to do it is:

ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';

TheALTER TABLE method requires anACCESS 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 thehstore type for the languages PL/Perl and PL/Python. The extensions for PL/Perl are calledhstore_plperl andhstore_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 extension for PL/Python is calledhstore_plpython3u. If you use it,hstore values are mapped to Python dictionaries.

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