1

I have following columns:

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

Is there a recommended way to turn this into hstore?

{foo => 1, bar => 2}
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:

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.