PostgreSQL：ON CONFLICT时如何不列全列更新原表空值列？

问题背景 #

读取大量数据导入PostgreSQL时，存在重复ID的数据，这些重复数据可填补已有记录的缺失值。当前写法需手动列出所有列，十分繁琐：

INSERT INTO tab(id,col1,col2,col3,...) VALUES (i,v1,v2,v3,...)
ON CONFLICT (id)
    DO UPDATE 
        SET 
            (col1,col2,col3, ...)=(
                COALESCE(tab.col1, EXCLUDED.col1),
                COALESCE(tab.col2, EXCLUDED.col2),
                COALESCE(tab.col3, EXCLUDED.col3),
                ...
             );

需要通用方法，无需手动枚举每一列，且适配多张表。同时疑惑是否该用INSERT命令，还是用UPDATE/JOIN替代。

PostgreSQL版本：psql (PostgreSQL) 12.14 (Ubuntu 12.14-0ubuntu0.20.04.1)

优雅解决方案：动态生成UPSERT语句 #

利用PostgreSQL系统表information_schema.columns获取表的列信息，动态生成INSERT ... ON CONFLICT语句，避免手动写每一列。

1. 生成单表的UPSERT语句 #

执行以下SQL获取自动生成的SET子句，直接替换到UPSERT语句中：

SELECT string_agg(
    format('%I = COALESCE(tab.%I, EXCLUDED.%I)', column_name, column_name, column_name),
    ', '
)
FROM information_schema.columns
WHERE table_name = 'tab'
  AND column_name != 'id'; -- 排除主键列

查询结果会输出类似col1 = COALESCE(tab.col1, EXCLUDED.col1), col2 = COALESCE(tab.col2, EXCLUDED.col2), ...的字符串，直接复用即可。

2. 通用脚本适配多张表 #

编写PL/pgSQL函数，传入表名和主键列名，自动生成UPSERT语句模板：

CREATE OR REPLACE FUNCTION generate_upsert_sql(p_table_name text, p_pk_column text)
RETURNS text AS $$
DECLARE
    v_columns text;
    v_set_clause text;
BEGIN
    -- 获取除主键外的所有列
    SELECT string_agg(format('%I', column_name), ', ')
    INTO v_columns
    FROM information_schema.columns
    WHERE table_name = p_table_name
      AND column_name != p_pk_column;

    -- 生成SET子句
    SELECT string_agg(
        format('%I = COALESCE(%1$I, EXCLUDED.%1$I)', column_name),
        ', '
    )
    INTO v_set_clause
    FROM information_schema.columns
    WHERE table_name = p_table_name
      AND column_name != p_pk_column;

    -- 返回完整UPSERT模板
    RETURN format(
        'INSERT INTO %I(%I, %s) VALUES ($1, $2, $3, ...)
         ON CONFLICT (%I)
         DO UPDATE SET %s;',
        p_table_name, p_pk_column, v_columns, p_pk_column, v_set_clause
    );
END;
$$ LANGUAGE plpgsql;

调用函数获取对应表的UPSERT语句：

SELECT generate_upsert_sql('tab', 'id');

得到的语句只需替换VALUES部分的参数，即可用于导入脚本。

关于INSERT vs UPDATE/JOIN的疑问 #

为什么用INSERT ... ON CONFLICT（UPSERT）更合适？ #

• 它能同时处理插入新记录和更新已有记录：无需先判断数据是否存在，一次语句完成两种操作，逻辑简洁且性能更优（减少两次查询的开销）。
• 你的场景是导入数据，既有新ID也有重复ID，UPSERT完美匹配"存在则更新，不存在则插入"的需求。

单独用UPDATE或JOIN的问题 #

• 只用UPDATE的话，需先确保记录存在，否则无法填补缺失值；对于新ID的数据，还要额外执行INSERT，逻辑拆分后代码更繁琐。
• 用JOIN的方式（如先导入临时表，再执行UPDATE+INSERT）步骤更多，需创建临时表、导入数据、执行UPDATE、再执行INSERT，不如UPSERT直接高效。

注意事项 #

• 确保主键（id）有唯一约束，否则ON CONFLICT无法生效。
• 若导入数据量极大，建议先导入临时表，再用批量UPSERT处理：

-- 假设临时表temp_tab和目标表tab结构一致
INSERT INTO tab(id, col1, col2, ...)
SELECT id, col1, col2, ... FROM temp_tab
ON CONFLICT (id)
DO UPDATE SET
    col1 = COALESCE(tab.col1, EXCLUDED.col1),
    col2 = COALESCE(tab.col2, EXCLUDED.col2),
    ...;

结合前面的动态SQL方法，同样可自动生成这里的SET子句。

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