PostgreSQL中INSERT ON CONFLICT如何批量更新所有列？

当然可以！既然TB_PO和temporary_table结构完全一致，你完全不用手动逐个敲完26+列，PostgreSQL提供了简洁的方式来实现批量更新。

方法1：PostgreSQL 12+ 推荐写法（最简洁） #

如果你使用的是PostgreSQL 12或更高版本，直接用行赋值就能搞定：

INSERT INTO TB_PO SELECT * FROM temporary_table
ON CONFLICT (id) DO UPDATE
SET TB_PO = excluded;

这里的excluded是PostgreSQL专门为ON CONFLICT子句提供的特殊行记录，它保存了原本要插入的冲突行数据。SET TB_PO = excluded会把目标表中冲突行的所有列直接替换为excluded对应的列值——因为两个表结构完全匹配，所有列都会自动正确映射，连列顺序都不用操心。

方法2：兼容旧版本（PostgreSQL 9.5-11） #

如果你的PostgreSQL版本低于12，可以利用ROW构造器和excluded.*来简化操作：

INSERT INTO TB_PO SELECT * FROM temporary_table
ON CONFLICT (id) DO UPDATE
SET (id, col2, col3, ..., col26) = ROW(excluded.*);

ROW(excluded.*)会把excluded行的所有字段打包成一个行类型，只要左边的列列表和目标表的列顺序完全一致（因为两个表结构相同，这个条件肯定满足），就能自动匹配所有列。

要是你连列列表都不想手动写，可以用动态SQL生成（适合在PL/pgSQL脚本中使用）：

DO $$
DECLARE
    col_list TEXT;
BEGIN
    -- 从information_schema中获取TB_PO的所有列名
    SELECT string_agg(column_name, ', ')
    INTO col_list
    FROM information_schema.columns
    WHERE table_name = 'tb_po' -- 注意这里表名是小写，因为information_schema存储的是小写
      AND table_schema = 'public'; -- 替换成你的实际schema名称

    -- 执行动态生成的插入更新语句
    EXECUTE format(
        'INSERT INTO TB_PO SELECT * FROM temporary_table
         ON CONFLICT (id) DO UPDATE
         SET (%s) = ROW(excluded.*)',
        col_list
    );
END $$;

注意事项 #

• 务必确保TB_PO和temporary_table的列顺序、数据类型完全一致，否则可能出现列不匹配的错误。
• 示例中更新了冲突列id，但因为excluded.id和目标行的id是相同的，所以不会有问题。如果你的业务逻辑不需要更新冲突列，可以在生成列列表时过滤掉id字段。

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