PostgreSQL中Upsert操作：如何分别获取插入与更新行数

要分别拿到Upsert操作里插入和更新的行数，你可以用以下几种实用方法实现：

方法一：利用PostgreSQL系统字段xmax统计 #

PostgreSQL内置的xmax字段可以帮我们区分行的操作类型：

• 新插入的行：xmax值为0（未被更新过）
• 冲突后更新的行：xmax值不为0（标记了更新操作的事务ID）

我们可以把Upsert语句放在CTE中，通过RETURNING返回操作类型，再统计数量：

WITH upsert_results AS (
    INSERT INTO tab1.summary(table_name, target_field, check_n)
    VALUES ('tab1', 'col1', 10),
           ('tab2', 'col2', 10)
    ON CONFLICT(table_name, target_field, check_n)
    DO UPDATE SET check_n = 20
    RETURNING 
        CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation_type
)
SELECT 
    operation_type,
    COUNT(*) AS row_count
FROM upsert_results
GROUP BY operation_type;

执行后会直接返回类似这样的结果集：

operation_type | row_count
----------------+-----------
 inserted       | 1
 updated        | 1

方法二：自定义标识字段（适合需长期记录操作类型的场景） #

如果允许修改表结构，可以新增一个辅助字段来明确标记操作类型：

1. 先添加字段：

ALTER TABLE tab1.summary ADD COLUMN last_operation VARCHAR(10);

2. 执行Upsert并标记操作类型：

WITH upsert_results AS (
    INSERT INTO tab1.summary(table_name, target_field, check_n, last_operation)
    VALUES ('tab1', 'col1', 10, 'inserted'),
           ('tab2', 'col2', 10, 'inserted')
    ON CONFLICT(table_name, target_field, check_n)
    DO UPDATE SET 
        check_n = 20,
        last_operation = 'updated'
    RETURNING last_operation AS operation_type
)
SELECT 
    operation_type,
    COUNT(*) AS row_count
FROM upsert_results
GROUP BY operation_type;

这种方法逻辑直观，还能保留每行的操作历史。

方法三：在PL/pgSQL中用GET DIAGNOSTICS统计 #

如果是在存储过程或函数里执行Upsert，可以先获取总行数，再通过查询统计更新行数，最后计算插入行数：

DECLARE
    total_affected integer;
    updated_count integer;
    inserted_count integer;
BEGIN
    -- 执行Upsert
    INSERT INTO tab1.summary(table_name, target_field, check_n)
    VALUES ('tab1', 'col1', 10),
           ('tab2', 'col2', 10)
    ON CONFLICT(table_name, target_field, check_n)
    DO UPDATE SET check_n = 20;
    
    -- 获取总受影响行数
    GET DIAGNOSTICS total_affected = ROW_COUNT;
    
    -- 统计更新的行数：匹配冲突条件的现有行数
    SELECT COUNT(*) INTO updated_count
    FROM tab1.summary
    WHERE (table_name, target_field, check_n) IN (('tab1', 'col1', 10), ('tab2', 'col2', 10));
    
    -- 计算插入行数
    inserted_count := total_affected - updated_count;
    
    -- 输出结果（可改为返回值或写入日志）
    RAISE NOTICE '插入行数: %, 更新行数: %', inserted_count, updated_count;
END;

注意：这种方法在高并发场景下可能有统计偏差，如果有其他会话同时修改相同数据，结果可能不准确。

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