PostgreSQL如何将文本字段迁移为多对多关联并插入关联表？

问题场景 #

现有两张业务表：

• offer表（含id主键与文本类型work_days字段）数据：

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字段）数据：

work_days
    id       work_days (text)
    1        full-week
    2        night-and-weekend

需要向offer_work_days关联表插入对应关系，最终得到：

offer_work_days
    offer_id    work_days_id
    45          2
    46          2
    47          1
    48          2

解决方案 #

使用INSERT ... SELECT语句，通过文本字段关联两张表，批量插入关联数据：

INSERT INTO offer_work_days (offer_id, work_days_id)
SELECT o.id, wd.id
FROM offer o
JOIN work_days wd ON o.work_days = wd.work_days;

补充说明 #

• 核心逻辑：通过o.work_days = wd.work_days的文本匹配，将每个offer记录关联到对应的work_days记录，直接提取双方主键插入关联表。
• 去重保障：如果关联表已存在部分数据，且(offer_id, work_days_id)设置了唯一约束，可添加ON CONFLICT子句避免重复插入：

INSERT INTO offer_work_days (offer_id, work_days_id)
SELECT o.id, wd.id
FROM offer o
JOIN work_days wd ON o.work_days = wd.work_days
ON CONFLICT (offer_id, work_days_id) DO NOTHING;

内容的提问来源于stack exchange，提问作者Aymeric