Copy text and find corresponding match to insert in another table [postgreql]-CodePudding

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)?

CodePudding user response：

demo:db<>fiddle

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