PostgreSQL批量插入时如何同时插入两张表并建立关联关系？

当然可以实现，PostgreSQL里可以借助**CTE（公共表表达式）**结合INSERT语句的RETURNING子句，一次性完成两张表的批量插入并建立关联。以下是具体实现方案：

基础版实现（无冲突处理） #

核心逻辑是先将collector表的数据拆分插入first表，同时返回生成的first.id，再通过关联原collector表数据，将对应字段和关联ID插入second表：

WITH inserted_first AS (
    -- 批量插入first表，同时返回插入生成的ID和关联collector的唯一标识
    INSERT INTO first (one, two)
    SELECT c.one, c.two
    FROM collector c
    RETURNING id AS first_id, one, two
)
-- 关联collector和插入后的first数据，批量插入second表并建立关联
INSERT INTO second (three, first_id)
SELECT c.three, if.first_id
FROM collector c
JOIN inserted_first if ON c.one = if.one AND c.two = if.two;

如果one、two字段不唯一，建议改用collector的主键id来关联，避免匹配错误：

WITH inserted_first AS (
    INSERT INTO first (one, two)
    SELECT 
        c.one, 
        c.two,
        c.id AS collector_id  -- 带上collector的唯一主键
    FROM collector c
    RETURNING id AS first_id, collector_id
)
INSERT INTO second (three, first_id)
SELECT c.three, if.first_id
FROM collector c
JOIN inserted_first if ON c.id = if.collector_id;

带冲突处理的实现 #

如果first表存在唯一约束（比如one、two组合唯一），需要保留原有的ON CONFLICT逻辑，可以这样修改：

-- 先给first表添加唯一约束（如果没有的话）
ALTER TABLE first ADD CONSTRAINT first_unique_one_two UNIQUE (one, two);

-- 批量 Upsert first表，再插入second表
WITH upserted_first AS (
    INSERT INTO first (one, two)
    SELECT one, two
    FROM collector
    ON CONFLICT (one, two) DO UPDATE 
        SET one = EXCLUDED.one, two = EXCLUDED.two  -- 根据需求更新字段
    RETURNING id AS first_id, one, two
)
INSERT INTO second (three, first_id)
SELECT c.three, uf.first_id
FROM collector c
JOIN upserted_first uf ON c.one = uf.one AND c.two = uf.two;

这种方式不管是插入新记录还是更新已有记录，都能拿到对应的first_id，确保second表的关联关系正确。

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