I try to migrate an existint text field into a many-to-many relationship.
offer id work_days (text) 45 night-and-weekend 46 night-and-weekend 47 full-week 48 night-and-weekend
work_days id work_days (text) 1 full-week 2 night-and-weekend
I would like to insert in
offer_work_days the correspondences to have this result
offer_work_days offer_id work_days_id 45 2 46 2 47 1 48 2
How can I achieve this with sql (on postgres)?
You have to
JOIN the tables on the
text column, return only the
id columns and insert the result into your new table.
INSERT INTO offer_work_days SELECT o.id, w.id FROM offer o JOIN work_days w ON o.work_days = w.work_days