PostgreSQL带唯一约束主表插入后同步写入关联表的问题

解决方案 #

你可以使用PostgreSQL原生的INSERT ... ON CONFLICT（Upsert）语法处理network_name的唯一键冲突，调整后的SQL如下：

WITH input_params AS (
    -- 统一管理待插入参数，避免重复硬编码
    SELECT 'ethereum'::text AS network_name, 'address2'::text AS address
),
insert_network AS (
    INSERT INTO networks (network_name)
    SELECT network_name FROM input_params
    -- 遇到重复network_name时跳过插入
    ON CONFLICT (network_name) DO NOTHING
    RETURNING network_id
)
INSERT INTO addresses (network_id, address)
SELECT
    -- 优先取新插入生成的network_id，取不到则查询已存在的记录
    COALESCE(
        (SELECT network_id FROM insert_network),
        (SELECT n.network_id FROM networks n JOIN input_params p ON n.network_name = p.network_name)
    ),
    address
FROM input_params
-- 如果address也有重复插入的场景，可以加下面这句跳过，不需要则删除
ON CONFLICT (address) DO NOTHING;

逻辑说明 #

1. input_params CTE统一存放本次操作需要用到的network_name和address，后续所有逻辑都从这里取值，减少写错的概率。
2. insert_network CTE插入网络数据时，如果检测到network_name重复就跳过插入，只有新插入的记录会返回对应的network_id。
3. 插入地址数据时通过COALESCE函数做兼容：如果是新插入的网络就用返回的network_id，如果网络已存在就直接从networks表查询已有的network_id完成地址插入。

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