I have following columns:
key | value
-----------
foo | 1
bar | 2
Is there a recommended way to turn this into hstore
?
{foo => 1, bar => 2}
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI have following columns:
key | value
-----------
foo | 1
bar | 2
Is there a recommended way to turn this into hstore
?
{foo => 1, bar => 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: