批量插入SQL如何实现基于uuid、name与subcustomer的条件插入或更新

你当前用的是PostgreSQL类的ON CONFLICT语法实现单字段冲突更新，要实现多优先级的冲突判断，推荐两种实现方式：

方案1：使用MERGE语句（PostgreSQL 15+ 推荐，完全匹配你的逻辑） #

MERGE支持自定义多条件匹配规则，完美契合你写的伪代码逻辑，批量插入场景下可以把待插入的批量数据作为源表直接匹配：

MERGE INTO customer t
USING (
    -- 这里放你的批量待插入数据，批量场景直接替换成VALUES列表即可
    VALUES 
        ('客户1', 'C001', '123456', '地址1', '100000', '朝阳区', 'CN', 'admin', 'admin', '子客户1', false, 'uuid1', false),
        ('客户2', 'C002', '654321', '地址2', '200000', '静安区', 'CN', 'admin', 'admin', '子客户2', false, null, false)
        -- 更多批量数据...
) AS s (name, customer_code, org_number, address, zip_code, zip_area, country_code, created_by, updated_by, subcustomer, credit_blocked, uuid, is_deleted)
-- 第一个匹配条件：uuid相等
ON t.uuid = s.uuid
WHEN MATCHED THEN
    UPDATE SET
        customer_code = s.customer_code,
        org_number = s.org_number,
        address = s.address,
        zip_code = s.zip_code,
        zip_area = s.zip_area,
        country_code = s.country_code,
        updated_by = s.updated_by,
        credit_blocked = s.credit_blocked,
        is_deleted = s.is_deleted
-- 第一个条件不匹配时，判断第二个条件：name和subcustomer都相等
WHEN NOT MATCHED AND EXISTS (
    SELECT 1 FROM customer t2 
    WHERE t2.name = s.name AND t2.subcustomer = s.subcustomer
) THEN
    UPDATE SET
        customer_code = s.customer_code,
        org_number = s.org_number,
        address = s.address,
        zip_code = s.zip_code,
        zip_area = s.zip_area,
        country_code = s.country_code,
        updated_by = s.updated_by,
        credit_blocked = s.credit_blocked,
        uuid = COALESCE(t.uuid, s.uuid), -- 保留原有uuid，没有的话用新值
        is_deleted = s.is_deleted
-- 两个条件都不匹配时执行插入
WHEN NOT MATCHED THEN
    INSERT (name, customer_code, org_number, address, zip_code, zip_area, country_code, created_by, updated_by, subcustomer, credit_blocked, uuid, is_deleted)
    VALUES (s.name, s.customer_code, s.org_number, s.address, s.zip_code, s.zip_area, s.country_code, s.created_by, s.updated_by, s.subcustomer, s.credit_blocked, s.uuid, s.is_deleted);

这个方案不需要修改表结构，逻辑和你写的伪代码完全一致，批量场景下直接扩展USING里的VALUES列表即可，性能比逐条操作高很多。

方案2：低版本PostgreSQL用ON CONFLICT实现 #

如果你的数据库版本低于15，不支持MERGE，可以通过添加两个部分唯一索引来实现：

1. 先创建两个部分唯一索引：

-- 针对非空uuid的唯一索引
CREATE UNIQUE INDEX idx_customer_uuid_notnull ON customer(uuid) WHERE uuid IS NOT NULL;
-- 针对uuid为空时的name+subcustomer联合唯一索引
CREATE UNIQUE INDEX idx_customer_name_subcustomer_uuid_null ON customer(name, subcustomer) WHERE uuid IS NULL;

2. 调整INSERT语句的ON CONFLICT逻辑：

INSERT INTO customer (name, customer_code, org_number, address, zip_code, zip_area, country_code, created_by, updated_by, subcustomer, credit_blocked, uuid, is_deleted)
VALUES 
    ('客户1', 'C001', '123456', '地址1', '100000', '朝阳区', 'CN', 'admin', 'admin', '子客户1', false, 'uuid1', false),
    ('客户2', 'C002', '654321', '地址2', '200000', '静安区', 'CN', 'admin', 'admin', '子客户2', false, null, false)
    -- 更多批量数据...
ON CONFLICT (uuid) WHERE uuid IS NOT NULL DO UPDATE SET
    customer_code = EXCLUDED.customer_code,
    org_number = EXCLUDED.org_number,
    address = EXCLUDED.address,
    zip_code = EXCLUDED.zip_code,
    zip_area = EXCLUDED.zip_area,
    country_code = EXCLUDED.country_code,
    updated_by = EXCLUDED.updated_by,
    credit_blocked = EXCLUDED.credit_blocked,
    is_deleted = EXCLUDED.is_deleted
-- 第二个冲突条件：name+subcustomer匹配且原记录uuid为空
ON CONFLICT (name, subcustomer) WHERE uuid IS NULL DO UPDATE SET
    customer_code = EXCLUDED.customer_code,
    org_number = EXCLUDED.org_number,
    address = EXCLUDED.address,
    zip_code = EXCLUDED.zip_code,
    zip_area = EXCLUDED.zip_area,
    country_code = EXCLUDED.country_code,
    updated_by = EXCLUDED.updated_by,
    credit_blocked = EXCLUDED.credit_blocked,
    -- 如果新数据有uuid就更新进去，后续就会走uuid冲突判断
    uuid = COALESCE(customer.uuid, EXCLUDED.uuid),
    is_deleted = EXCLUDED.is_deleted;

这个方案的注意点：

• 只对原表中uuid IS NULL的记录走name+subcustomer匹配，符合你的需求
• 如果待插入的记录有uuid，冲突判断优先走uuid索引，和你的逻辑一致
• 批量插入和原有写法差异很小，改下冲突条件即可

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