PG库唯一键冲突的插入方式

PG库唯一键冲突的插入方式

1 单条插入

<!-- 唯一键(doc_id, tenant_id) -->
<insert id="insertMqDeadLetterSalesOrder" parameterType="MqDeadLetterSalesOrder" useGeneratedKeys="true" keyProperty="id">
    insert into erp_mq_dead_letter_sales_order
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="docId != null">doc_id,</if>
        <if test="createTime != null">create_time,</if>
        <if test="tenantId != null">tenant_id,</if>
        <if test="docType != null and docType != ''">doc_type,</if>
        <if test="docNo != null and docNo != ''">doc_no,</if>

    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="docId != null">#{docId},</if>
        <if test="createTime != null">#{createTime},</if>
        <if test="tenantId != null">#{tenantId},</if>
        <if test="docType != null and docType != ''">#{docType},</if>
        <if test="docNo != null and docNo != ''">#{docNo},</if>
    </trim>
    ON CONFLICT (doc_id, tenant_id)
    DO UPDATE SET
        create_time = now()
</insert>

2 批量插入

<!--  唯一键  (tenant_id, doc_id, doc_type, queue_type) -->
<insert id="batchInsertRecords" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO erp_mq_dead_letter_inventory (
        queue_type,
        doc_id,
        doc_type,
        create_time,
        tenant_id,
        doc_no,
        remark
    )
    VALUES
    <foreach collection="list" item="item" separator=",">
        (
            #{item.queueType},
            #{item.docId},
            #{item.docType},
            #{item.createTime},
            #{item.tenantId},
            #{item.docNo},
            #{item.remark}
        )
    </foreach>
    ON CONFLICT (tenant_id, doc_id, doc_type, queue_type)
    DO UPDATE SET
        create_time = now()
</insert>

3 这种方式处理弊端：

这种方式，由于 ON CONFLICT ， 只要走了这里，整个sql不会返回 id !!!