如何用Postgres CTE或高效方式实现主从表批量插入关联

现有两张表结构如下：

Table_1: id name age

Table_2: id table_1_id city state

需要先向Table_1批量插入数据：

INSERT INTO Table_1(name, age)
VALUES ('A', 12), ('B', 13), ('C', 14)
RETURNING id

再将上述插入返回的id对应用于Table_2的插入：

INSERT INTO Table_2(table_1_id, city, state)
VALUES (first_row_id, 'Austin', 'Texas'), 
(second_row_id, 'Dallas', 'Texas'), 
(third_row_id, 'Houston', 'Texas')

请问如何在Postgres中使用CTE或其他高效且代码量最少的方式实现该操作？

在PostgreSQL里，用CTE结合INSERT ... RETURNING就能一次性搞定这个关联插入，既高效又简洁。

方式一：用CASE匹配关联数据 #

先在CTE里完成Table_1的插入并返回生成的id和name，再通过CASE语句把每个name对应到Table_2的城市数据，确保关联准确：

WITH inserted_table1 AS (
    INSERT INTO Table_1(name, age)
    VALUES ('A', 12), ('B', 13), ('C', 14)
    RETURNING id, name
)
INSERT INTO Table_2(table_1_id, city, state)
SELECT 
    it1.id, 
    CASE it1.name
        WHEN 'A' THEN 'Austin'
        WHEN 'B' THEN 'Dallas'
        WHEN 'C' THEN 'Houston'
    END AS city,
    'Texas' AS state
FROM inserted_table1 it1;

方式二：预定义关联数据集 #

如果数据量较大，或者不想用CASE分支，可以先把Table_1和Table_2的对应数据拼成一个统一的源数据集，再分别插入两张表，这种方式扩展性更好：

WITH source_data AS (
    SELECT 'A' AS name, 12 AS age, 'Austin' AS city, 'Texas' AS state
    UNION ALL
    SELECT 'B' AS name, 13 AS age, 'Dallas' AS city, 'Texas' AS state
    UNION ALL
    SELECT 'C' AS name, 14 AS age, 'Houston' AS city, 'Texas' AS state
), inserted_table1 AS (
    INSERT INTO Table_1(name, age)
    SELECT name, age FROM source_data
    RETURNING id, name
)
INSERT INTO Table_2(table_1_id, city, state)
SELECT it1.id, sd.city, sd.state
FROM inserted_table1 it1
JOIN source_data sd ON it1.name = sd.name;

这两种方法都能保证每一行Table_1的数据对应正确的Table_2记录，而且全程单SQL执行，避免了多次数据库交互的开销，是最高效的实现方式。

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