Create hstore from key-value result set

Question

I have following columns:

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

Is there a recommended way to turn this into hstore?

{foo => 1, bar => 2}

Answer 1

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?