What SQL query will translate a PostgreSQL hstore column into a regular table where the distinct keys in all of the hstore rows form the columns in the table and the values fill the rows in the corresponding column?
For example, how can I convert hstore column
hstore
------------------------------------
"a"=>"1", "b"=>"2", "c"=>"3"
"a"=>"4", "b"=>"6", "c"=>"5", "d"=>7
"a"=>"8", "b"=>"3", "c"=>"8"
into "equivalent" table
a b c d
---------------
1 | 2 | 3 |
4 | 6 | 5 | 7
8 | 3 | 8 |
where the distinct hstore keys a, b, c, and d form the columns in the table and their values fill the rows in each column?
You can't do this dynamically, but you can do this, if you create a type that matches your output.
create table foo (data hstore);
create type foo_type as (a text, b text, c text);
insert into foo (data)
values
('a => 1, b => 2'),
('a => 1, b=>2, c => 3'),
('a => 1');
select (populate_record(null::foo_type, data)).*
from foo;
returns:
a | b | c --+---+-- 1 | 2 | 1 | 2 | 3 1 | |
Note that you can also use a "table type" if you have a table that matches the columns you want.