Create hstore from key-value result set I have following columns:

key | value
-----------
foo | 1
bar | 2


Is there a recommended way to turn this into hstore?

{foo =&gt; 1, bar =&gt; 2}
Just use the function hstore().

There are several overlaoded versions of that functions. One takes a single text array with keys and values. Another one takes two text arrays, one with keys, the other one with values. Either works for you. Demo:

SELECT hstore(array_agg(ARRAY[key, value::text]))     AS option1
     , hstore(array_agg(key), array_agg(value::text)) AS option2
FROM  (VALUES ('foo', 1), ('bar', 2)) AS t(key, value);


option1 requires Postgres 9.5 or later, where array_agg() for multidimensional arrays was introduced. For older versions see:


Is there something like a zip() function in PostgreSQL that combines two arrays?


If value is not type text, you may need the cast I added: value::text

The additional module hstore has to be installed in the database, of course. 

CREATE EXTENSION IF NOT EXISTS hstore;


Related:


Assignment of a column with dynamic column name
Is it recommended to install extensions into pg_catalog schema?