CODEWITHSUNDEEP
sqlpostgresqlhstore
Derek Mahar
Derek Mahar

Reputation: 28386

How can I translate a PostgreSQL hstore column into a row?

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?

Upvotes: 4

Views: 2965

Answers (2)

Jayme Tosi Neto
Jayme Tosi Neto

Reputation: 1239

I know the question is answered, but I've run into a similar problem. I needed to convert hstore values so I could directly insert the data into a table. This is the solution I've gotten:

INSERT INTO foo 
SELECT (populate_record(null::foo, 'a => 1, b=>2, c => 3'::hstore)).*;

Of course, the hstore columns must be in the table or they'll be ignored. Hope this helps other people who ended up here like me. :)

Upvotes: 2

user330315
user330315

Reputation:

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.

Upvotes: 5

Related Questions