How to break apart Hstore keys and values into separate columns in postgres

Question

I have an osm table with all the tags found in the hstore column named tags.

I want to write a query that will return a table with the all the columns of the table and additional columns for each tag having each key as the column name and the value under it. How do I go about that?

So my input will be:

Item_id | tags |
----------+-------+
1614     | apple=2,bees=150|
1938     | apple=1,bees=50  |
1983     | apple=1,bees=50  |
1322     | apple=1,bees=100|

output will be of the script

item_id | apple |  bees 
---------+-------+------
   1614  |   2   |  150
   1938  |   1   |  50
   1983  |   1   |  50
   1322  |   1   |  100

taking into account that each row have a varied amount of tags and i dont know what are the counts and what are the keys attached to each column so how do I go about so for example

     Item_id | tags |
    ----------+-------+
    1614     | apple=2,bees=150,green=345|
    1938     | apple=1,bees=50  |
    1983     | apple=1,bees=50  |
    1322     | apple=1,bees=100,red=346|

output will be:

  item_id | apple |  bees |  green | red|
    ---------+-------+----+--------+----+
       1614  |   2   |  150 |345| NULL|
       1938  |   1   |  50  |NULL| NULL|
       1983  |   1   |  50  |NULL| NULL|
       1322  |   1   |  100 |NULL| 346|

Answer 1

Here is a function that accepts a table name, and a list of keys. It then creates the new table and columns. It demonstrates how to do dynamic commands within the function. The SELECT at the end shows how to extract your keys. Populating the table can be done by looping through the rows of your existing table after creating your columns. There is almost certainly a more elegant/efficient way to do all of this :)

CREATE OR REPLACE FUNCTION explode_hstore(tname text, keys TEXT[]) RETURNS VOID AS $$ DECLARE k TEXT; BEGIN RAISE NOTICE 'Creating table=%', tname; EXECUTE 'CREATE TABLE ' || tname || ' ()'; FOREACH k IN ARRAY keys LOOP RAISE NOTICE 'Adding column for key=%', k; EXECUTE 'ALTER TABLE ' || tname || ' ADD COLUMN ' || k || ' TEXT'; END LOOP; END; $$ LANGUAGE plpgsql;

To run the function:

SELECT explode_hstore('exploded_hstore', (select array_agg(x.keys) FROM (SELECT skeys("data") AS keys FROM hstoretest GROUP BY keys) x))