PostgreSQL：如何获取插入行数并写入日志表？

问题场景 #

需要将tb2表的内容复制到tb1表，同时把插入的行数写入month_log日志表，原尝试的SQL因在RETURNING子句中使用聚合函数count(*)报错：

ERROR: aggregate functions are not allowed in RETURNING
LINE 4: returning count(*) as num_rows_inserted
^
SQL state: 42803
Character: 109

解决方案 #

方法1：纯SQL（CTE返回行后统计）

通过CTE返回所有被插入的行（这里用RETURNING 1简化，也可以返回表的任意列），再在后续插入日志表时统计行数：

WITH inserted_rows AS (
    INSERT INTO tb1 OVERRIDING USER VALUE
    SELECT * FROM tb2
    RETURNING 1
)
INSERT INTO month_log (num_rows_inserted)
SELECT COUNT(*) FROM inserted_rows;

这种方式完全用SQL完成，无需PL/pgSQL，适合单次执行的场景。

方法2：PL/pgSQL块（获取诊断信息）

如果需要更灵活的逻辑，可使用PL/pgSQL块，通过GET DIAGNOSTICS获取INSERT语句影响的行数：

DO $$
DECLARE
    inserted_count INTEGER;
BEGIN
    -- 执行数据复制
    INSERT INTO tb1 OVERRIDING USER VALUE
    SELECT * FROM tb2;
    
    -- 获取插入行数
    GET DIAGNOSTICS inserted_count = ROW_COUNT;
    
    -- 写入日志表
    INSERT INTO month_log (num_rows_inserted)
    VALUES (inserted_count);
END $$;

这种方法适合需要添加额外逻辑（比如判断行数是否符合预期）的场景。

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