PostgreSQL插入新行时如何同时处理主键与外键冲突？

问题场景 #

plays表结构如下：

play_id: primary key, varchar, uuid
event_id: foreign key, varchar
player_id: foreign key, varchar
date: Date object
time: timestamp object

插入数据时会遇到两类冲突：

• 主键play_id重复（主键约束冲突）
• event_id/player_id在关联表events/players中不存在（外键约束冲突）

尝试的SQL语句（存在语法错误）：

INSERT INTO plays (play_id, event_id, player_id, date, time)
VALUES ('e092068d-edc5-4e67-99ea-b2b429eaa4f0', '12345', '54321', '2023-01-01', '08:23:34.23'),
('027d427b-ca50-4756-8513-185f1bf69665', '418590', '16263', '2023-03-12', '12:04:53.00'),
('03c67512-5529-4142-b443-f7994ed7b2bc', '418590', '16263', '2023-03-16', '10:31:31.70'),
('06885f46-b3e7-43ce-be00-a08fc9e220b6', '443484', '17057', '2023-03-28', '17:17:18.63')
ON CONFLICT (play_id) DO UPDATE SET play_id = EXCLUDED.play_id, event_id = EXCLUDED.event_id, player_id = EXCLUDED.player_id, date = EXCLUDED.date, time = EXCLUDED.time
ON CONFLICT ON CONSTRAINT plays_event_id_fkey DO NOTHING
ON CONFLICT ON CONSTRAINT plays_player_id_fkey DO NOTHING;

执行时报错：第二个ON附近存在语法错误，原因是PostgreSQL不支持单条INSERT语句使用多个ON CONFLICT子句。

解决方案 #

方案一：提前过滤合法行+处理主键冲突 #

通过关联events和players表，提前过滤掉外键不存在的行，再对合法行执行插入+主键冲突更新：

INSERT INTO plays (play_id, event_id, player_id, date, time)
SELECT v.play_id, v.event_id, v.player_id, v.date, v.time
FROM (
    VALUES 
        ('e092068d-edc5-4e67-99ea-b2b429eaa4f0', '12345', '54321', '2023-01-01'::date, '08:23:34.23'::timestamp),
        ('027d427b-ca50-4756-8513-185f1bf69665', '418590', '16263', '2023-03-12'::date, '12:04:53.00'::timestamp),
        ('03c67512-5529-4142-b443-f7994ed7b2bc', '418590', '16263', '2023-03-16'::date, '10:31:31.70'::timestamp),
        ('06885f46-b3e7-43ce-be00-a08fc9e220b6', '443484', '17057', '2023-03-28'::date, '17:17:18.63'::timestamp)
) AS v(play_id, event_id, player_id, date, time)
JOIN events e ON e.event_id = v.event_id
JOIN players p ON p.player_id = v.player_id
ON CONFLICT (play_id) DO UPDATE 
SET event_id = EXCLUDED.event_id, 
    player_id = EXCLUDED.player_id, 
    date = EXCLUDED.date, 
    time = EXCLUDED.time;

优势：批量过滤+插入，性能更高，避免异常抛出。

方案二：逐行插入+捕获异常跳过冲突 #

通过PL/pgSQL存储过程逐行尝试插入，捕获主键/外键冲突并分别处理：

CREATE OR REPLACE FUNCTION insert_plays_with_skip()
RETURNS void AS $$
DECLARE
    rec record;
BEGIN
    FOR rec IN 
        VALUES 
            ('e092068d-edc5-4e67-99ea-b2b429eaa4f0', '12345', '54321', '2023-01-01'::date, '08:23:34.23'::timestamp),
            ('027d427b-ca50-4756-8513-185f1bf69665', '418590', '16263', '2023-03-12'::date, '12:04:53.00'::timestamp),
            ('03c67512-5529-4142-b443-f7994ed7b2bc', '418590', '16263', '2023-03-16'::date, '10:31:31.70'::timestamp),
            ('06885f46-b3e7-43ce-be00-a08fc9e220b6', '443484', '17057', '2023-03-28'::date, '17:17:18.63'::timestamp)
    LOOP
        BEGIN
            INSERT INTO plays (play_id, event_id, player_id, date, time)
            VALUES (rec.column1, rec.column2, rec.column3, rec.column4, rec.column5)
            ON CONFLICT (play_id) DO UPDATE 
            SET event_id = EXCLUDED.event_id, 
                player_id = EXCLUDED.player_id, 
                date = EXCLUDED.date, 
                time = EXCLUDED.time;
        EXCEPTION
            WHEN foreign_key_violation THEN
                CONTINUE; -- 忽略外键冲突，继续下一行
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 调用存储过程执行插入
SELECT insert_plays_with_skip();

优势：逻辑灵活，可单独处理每一行的异常场景，适合需要保留尝试记录的需求。

关于自定义约束 #

不需要创建额外的自定义约束，上述两种方案已覆盖需求：

• 批量插入优先选择方案一，性能更优
• 需要灵活处理个别行异常时选择方案二

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