hstore Functions

    In the current implementation, neither the key nor the value string can exceed 65535 bytes in length; an error will be thrown if this limit is exceeded. These maximum lengths may change in future releases.

    Before you can use hstore data type and functions, run the installation script $GPHOME/share/postgresql/contrib/hstore.sql in each database where you want the ability to query other databases:

    hstore External Representation

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

    1. k => v
    2. foo => bar, baz => whatever
    3. "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.)

      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.

      Note: The => operator is deprecated and may be removed in a future release. Use the hstore(text, text) function instead.

      Table 2. hstore Functions
      FunctionReturn TypeDescriptionExampleResult
      hstore(text, text)hstoremake single-item hstorehstore(‘a’, ‘b’)“a”=>”b”
      akeys(hstore)text[]get hstore‘s keys as arrayakeys(‘a=>1,b=>2’){a,b}
      skeys(hstore)setof textget hstore‘s keys as setskeys(‘a=>1,b=>2’)
      avals(hstore)text[]get hstore‘s values as arrayavals(‘a=>1,b=>2’){1,2}
      svals(hstore)setof textget hstore‘s values as setsvals(‘a=>1,b=>2’)
      2. 2
      each(hstore)setof (key text, value text)get hstore‘s keys and values as setselect * from each(‘a=>1,b=>2’) key|value
       ————-+——————-
        a | 1
        b | 2
      exist(hstore,text)booleandoes hstore contain key?exist(‘a=>1’,’a’)t
      defined(hstore,text)booleandoes hstore contain non-null value for key?defined(‘a=>NULL’,’a’)f
      delete(hstore,text)hstoredelete any item matching keydelete(‘a=>1,b=>2’,’b’)“a”=>”1”

      Indexes

      hstore has index support for @> and ? operators. You can use the GiST index type. For example:

      1. CREATE INDEX hidx ON testhstore USING GIST(h);

      Delete a key:

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

      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:

      1. SELECT * FROM each('aaa=>bq, b=>NULL, ""=>1');

      Using a table:

      1. SELECT key, count(*) FROM
      2.   (SELECT (each(h)).key FROM testhstore) AS stat
      3.   GROUP BY key
      4.   ORDER BY count DESC, key;
      5. -----------+-------
      6.  line      |   883
      7.  query     |   207
      8.  pos       |   203
      9.  node      |   202
      10.  space     |   197
      11.  status    |   195
      12.  public    |   194
      13.  title     |   190
      14. ...................

      Parent topic: Additional Supplied Modules