CODEWITHSUNDEEP
postgresqlhstore
robert trudel
robert trudel

Reputation: 5779

Insert based on select of hstore column

In try to insert value from a hstore (postgreql) to a more generic table

In my car table, I have theses fields

id
fields (hstore)

My store table, I have theses fields

id
key
value
car_id
date

How to loop to my fields property in insert key, value to my store table.

Is there a way to do it with a select command?

Upvotes: 0

Views: 567

Answers (1)

klin
klin

Reputation: 121814

Example data:

insert into car values
(1, 'brand=>ford, color=>yellow'),
(2, 'brand=>volvo, mileage=>50000, year=>2015');

Use the function each(hstore) to get pairs (key, value) of hstore column:

select id, key, value
from car, each(fields);

 id |   key   | value  
----+---------+--------
  1 | brand   | ford
  1 | color   | yellow
  2 | year    | 2015
  2 | brand   | volvo
  2 | mileage | 50000
(5 rows)

The insert command may look like this:

insert into store (car_id, key, value)
select id, key, value
from car, each(fields);

Upvotes: 3

Related Questions