Andrew Gierth (RhodiumToad on irc) wrote, and Tom Lane committed:

Assorted improvements in contrib/hstore.
 
Remove the 64K limit on the lengths of keys and values within an hstore.
(This changes the on-disk format, but the old format can still be read.)
Add support for btree/hash opclasses for hstore --- this is not so much
for actual indexing purposes as to allow use of GROUP BY, DISTINCT, etc.
Add various other new functions and operators.
 
Andrew Gierth

So, I assume that you know/understand what hstore is. If not – please read the docs, but in short it can be said that it's a hash (associative array, dictionary) in single field in PostgreSQL.

So, Andrew added some new things. But what things?

First of all – you can use hstore for group/distinct. And this is very cool. Let me show you how it works:

# CREATE TABLE test (h hstore);
# INSERT INTO test (h) VALUES ('a=>123');
# INSERT INTO test (h) VALUES ('a=>123');
# INSERT INTO test (h) VALUES ('a=>256');
# INSERT INTO test (h) VALUES ('a=>256,b=>512');
# SELECT h, COUNT(*) FROM test GROUP BY h;
           h            │ COUNT
────────────────────────┼───────
 "a"=>"123"             │     2
 "a"=>"256", "b"=>"512" │     1
 "a"=>"256"             │     1
(3 ROWS)

Of course you'll usually want some finer grained statistics, but if you have relatively low number of various fields in hstore column – it can be quite good way to check what's what.

As for new functions and operators, let me show you some:

• -> operator (which is used to get value from hstore, for given key) works now also with arrays:

  # SELECT 'a=>1, b=>2, c=>3, d=>4'::hstore -> ARRAY['b', 'd', 'c'];
   ?COLUMN?
  ──────────
   {2,4,3}
  (1 ROW)

  And even multi-dimensional arrays:

  # SELECT 'a=>1, b=>2, c=>3, d=>4'::hstore -> ARRAY[['b', 'd'],['c', 'd']];
     ?COLUMN?
  ───────────────
   {{2,4},{3,4}}
  (1 ROW)

• ? operator checks if given key exists in hstore:

  # SELECT 'a=>1, b=>2, c=>3, d=>4'::hstore ? 'a';
   ?COLUMN?
  ──────────
   t
  (1 ROW)
   
  (depesz@[LOCAL]:5850) 13:43:42 [depesz]
  # SELECT 'a=>1, b=>2, c=>3, d=>4'::hstore ? 'e';
   ?COLUMN?
  ──────────
   f
  (1 ROW)

• ?| and ?& operators – which take array, and check if any or all (?| -> any, ?& -> all) keys from array are in hstore:

  # WITH t AS ( SELECT 'a=>1, b=>2, c=>3, d=>4'::hstore AS h )
  SELECT h ?| array['a', 'b'], h ?| array['a','x'], h ?| array['x', 'y'] FROM t;
   ?COLUMN? │ ?COLUMN? │ ?COLUMN?
  ──────────┼──────────┼──────────
   t        │ t        │ f
  (1 ROW)
   
  # WITH t AS ( SELECT 'a=>1, b=>2, c=>3, d=>4'::hstore AS h )
  SELECT h ?& array['a', 'b'], h ?& array['a','x'], h ?& array['x', 'y'] FROM t;
   ?COLUMN? │ ?COLUMN? │ ?COLUMN?
  ──────────┼──────────┼──────────
   t        │ f        │ f
  (1 ROW)

• – new operator, which works as delete(hstore, …), and delete() has been extended to support deletes of arrays and hstores:

  # WITH t AS ( SELECT 'a=>1, b=>2, c=>3, d=>4'::hstore AS h )
  SELECT h - 'a'::text, h - ARRAY['a', 'c'], h - 'b=>2,e=>5,d=>10'::hstore FROM t;
             ?COLUMN?           │      ?COLUMN?      │           ?COLUMN?
  ──────────────────────────────┼────────────────────┼──────────────────────────────
   "b"=>"2", "c"=>"3", "d"=>"4" │ "b"=>"2", "d"=>"4" │ "a"=>"1", "c"=>"3", "d"=>"4"
  (1 ROW)

  Important note – please see that you have to cast to text datatype when substracting text from hstore.

• =\> operator – new functionality – build multi-key hstores in single call:

  # SELECT ARRAY['a','b','c'] => ARRAY['1','2','3'];
             ?COLUMN?
  ──────────────────────────────
   "a"=>"1", "b"=>"2", "c"=>"3"
  (1 ROW)

  or even getting slices of hstore:

  # SELECT 'a=>1, b=>2, c=>3, d=>4'::hstore => ARRAY['a','b','e'];
        ?COLUMN?
  ────────────────────
   "a"=>"1", "b"=>"2"
  (1 ROW)

• conversion from arrays to hstore:

  # SELECT array['a','1','b','2','c','3']::hstore;
              array
  ──────────────────────────────
   "a"=>"1", "b"=>"2", "c"=>"3"
  (1 ROW)

  Of course it breaks for odd number of elements:

  # SELECT array['a','1','b','2','c']::hstore;
  ERROR:  array must have even NUMBER OF elements

  Also – you can do the conversion from multidimensional array:

  # SELECT array[['a','1'],['b','2'],['c','3'],['d','4']]::hstore;
                   array
  ────────────────────────────────────────
   "a"=>"1", "b"=>"2", "c"=>"3", "d"=>"4"
  (1 ROW)

  These operations can be reversed with hstore_to_array() and hstore_to_matrix() functions.

• Conversion of records to hstore:

  # SELECT hstore(record) FROM pg_shdescription AS record;
                                      hstore
  ───────────────────────────────────────────────────────────────────────────────
   "objoid"=>"1", "classoid"=>"1262", "description"=>"default template database"
  (1 ROW)

And probably some others I missed, just check the diff of sql.