PostgreSQL 8.3.1 Documentation
Prev | Fast Backward | Appendix F. Additional Supplied Modules | Fast Forward | Next

# F.10. hstore

This module implements a data type `hstore` for storing sets of (key,value) pairs within a single PostgreSQL data field. This can be useful in various scenarios, such as rows with many attributes that are rarely examined, or semi-structured data.

## F.10.1. `hstore` External Representation

The text representation of an `hstore` value includes zero or more `key` `=>` `value` items, separated by commas. For example:

        k => v
        foo => bar, baz => whatever
        "1-a" => "anything at all"

The order of the items is not considered significant (and may not be reproduced on output). Whitespace between items or around the `=>` sign is ignored. Use double quotes if a key or value includes whitespace, comma, `=` or `>`. To include a double quote or a backslash in a key or value, precede it with another backslash. (Keep in mind that depending on the setting of `standard_conforming_strings`, you may need to double backslashes in SQL literal strings.)

A value (but not a key) can be a SQL NULL. This is represented as

        key => NULL

The `NULL` keyword is not case-sensitive. Again, use double quotes if you want the string `null` to be treated as an ordinary data value.

Currently, double quotes are always used to surround key and value strings on output, even when this is not strictly necessary.

## F.10.2. `hstore` Operators and Functions

Table F-5. `hstore` Operators

Operator | Description | Example | Result
--- | --- | --- | ---
`hstore` `->` `text` | get value for key (null if not present) | `'a=>x, b=>y'::hstore -> 'a'` | `x`
`text` `=>` `text` | make single-item `hstore` | `'a' => 'b'` | `"a"=>"b"`
`hstore` `||` `hstore` | concatenation | `'a=>b, c=>d'::hstore || 'c=>x, d=>q'::hstore` | `"a"=>"b", "c"=>"x", "d"=>"q"`
`hstore` `?` `text` | does `hstore` contain key? | `'a=>1'::hstore ? 'a'` | `t`
`hstore` `@>` `hstore` | does left operand contain right? | `'a=>b, b=>1, c=>NULL'::hstore @> 'b=>1'` | `t`
`hstore` `<@` `hstore` | is left operand contained in right? | `'a=>c'::hstore <@ 'a=>b, b=>1, c=>NULL'` | `f`

(Before PostgreSQL 8.2, the containment operators @> and <@ were respectively called @ and ~. These names are still available, but are deprecated and will eventually be retired. Notice that the old names are reversed from the convention formerly followed by the core geometric datatypes!)

Table F-6. `hstore` Functions

Function | Return Type | Description | Example | Result
--- | --- | --- | --- | ---
`akeys(hstore)` | `text[]` | get `hstore`'s keys as array | `akeys('a=>1,b=>2')` | `{a,b}`
`skeys(hstore)` | `setof text` | get `hstore`'s keys as set | `skeys('a=>1,b=>2')` |
    a
    b
`avals(hstore)` | `text[]` | get `hstore`'s values as array | `avals('a=>1,b=>2')` | `{1,2}`
`svals(hstore)` | `setof text` | get `hstore`'s values as set | `svals('a=>1,b=>2')` |
    1
    2
`each(hstore)` | `setof (key text, value text)` | get `hstore`'s keys and values as set | `select * from each('a=>1,b=>2')` |
     key | value
    -----+-------
     a   | 1
     b   | 2
`exist(hstore,text)` | `boolean` | does `hstore` contain key? | `exist('a=>1','a')` | `t`
`defined(hstore,text)` | `boolean` | does `hstore` contain non-null value for key? | `defined('a=>NULL','a')` | `f`
`delete(hstore,text)` | `hstore` | delete any item matching key | `delete('a=>1,b=>2','b')` | `"a"=>"1"`

## F.10.3. Indexes

`hstore` has index support for `@>` and `?` operators. You can use either GiST or GIN index types. For example:

    CREATE INDEX hidx ON testhstore USING GIST(h);

    CREATE INDEX hidx ON testhstore USING GIN(h);

## F.10.4. Examples

Add a key, or update an existing key with a new value:

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

Delete a key:

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

## F.10.5. Statistics

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. Examples below 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
    ...................

## F.10.6. Authors

Oleg Bartunov `<oleg@sai.msu.su>`, Moscow, Moscow University, Russia

Teodor Sigaev `<teodor@sigaev.ru>`, Moscow, Delta-Soft Ltd., Russia

Prev | Home | Next
fuzzystrmatch | Up | intagg