added postgresql documentation supporting my answer
Source Link

Based on s.m.'s answer it seems you can combine EACH() with both .key and .value to generate columns automatically.

Setup test table like in Ian Timothy's answer:

create table hstore_test (id serial, hstore_col hstore);
insert into hstore_test (hstore_col) values ('key1=>val11, key2=>val12, key3=>val13'), ('key1=>val21, key2=>val22');

Query

SELECT id, (EACH(hstore_col)).key, (EACH(hstore_col)).value FROM hstore_test;

which returns

 id | key  | value 
----+------+-------
  1 | key1 | val11
  1 | key2 | val12
  1 | key3 | val13
  2 | key1 | val21
  2 | key2 | val22

I couldn't confirm from documentation that thewas a bit unsure whether two each()EACH() inside the SELECTsame query are guaranteed to haveproduce the same order but it does the right thing when I try it in Postgresql version 10.12. If someone finds the docs I can updatefound an example in the hstore documentation using the answersame mechanism so I guess it's explicitly supported.

Based on s.m.'s answer it seems you can combine EACH() with both .key and .value to generate columns automatically.

Setup test table like in Ian Timothy's answer:

create table hstore_test (id serial, hstore_col hstore);
insert into hstore_test (hstore_col) values ('key1=>val11, key2=>val12, key3=>val13'), ('key1=>val21, key2=>val22');

Query

SELECT id, (EACH(hstore_col)).key, (EACH(hstore_col)).value FROM hstore_test;

which returns

 id | key  | value 
----+------+-------
  1 | key1 | val11
  1 | key2 | val12
  1 | key3 | val13
  2 | key1 | val21
  2 | key2 | val22

I couldn't confirm from documentation that the two each() inside the SELECT are guaranteed to have the same order but it does the right thing when I try it in Postgresql version 10.12. If someone finds the docs I can update the answer.

Based on s.m.'s answer it seems you can combine EACH() with both .key and .value to generate columns automatically.

Setup test table like in Ian Timothy's answer:

create table hstore_test (id serial, hstore_col hstore);
insert into hstore_test (hstore_col) values ('key1=>val11, key2=>val12, key3=>val13'), ('key1=>val21, key2=>val22');

Query

SELECT id, (EACH(hstore_col)).key, (EACH(hstore_col)).value FROM hstore_test;

which returns

 id | key  | value 
----+------+-------
  1 | key1 | val11
  1 | key2 | val12
  1 | key3 | val13
  2 | key1 | val21
  2 | key2 | val22

I was a bit unsure whether two EACH() inside the same query are guaranteed to produce the same order. I found an example in the hstore documentation using the same mechanism so I guess it's explicitly supported.

Source Link

Based on s.m.'s answer it seems you can combine EACH() with both .key and .value to generate columns automatically.

Setup test table like in Ian Timothy's answer:

create table hstore_test (id serial, hstore_col hstore);
insert into hstore_test (hstore_col) values ('key1=>val11, key2=>val12, key3=>val13'), ('key1=>val21, key2=>val22');

Query

SELECT id, (EACH(hstore_col)).key, (EACH(hstore_col)).value FROM hstore_test;

which returns

 id | key  | value 
----+------+-------
  1 | key1 | val11
  1 | key2 | val12
  1 | key3 | val13
  2 | key1 | val21
  2 | key2 | val22

I couldn't confirm from documentation that the two each() inside the SELECT are guaranteed to have the same order but it does the right thing when I try it in Postgresql version 10.12. If someone finds the docs I can update the answer.