Postgres MERGE实现匹配更新、无列名插入的方法求助

直接用INSERT SELECT src.*替代VALUES(src.*) #

PostgreSQL 13/14的MERGE不支持VALUES(src.*)这种行记录直接传入的写法，但可以通过INSERT ... SELECT实现无列名指定的插入。因为你的源视图和目标表结构完全一致，src.*的列顺序和目标表定义顺序完全匹配，无需手动指定列名。

示例MERGE语句（需替换实际匹配条件）：

MERGE INTO my_table tgt
USING my_view src
ON tgt.business_unique_key = src.business_unique_key -- 替换为你的实际业务匹配键（不能是要更新的ID）
WHEN MATCHED THEN
  UPDATE SET id = src.id, cname = src.cname
WHEN NOT MATCHED THEN
  INSERT SELECT src.*;

核心逻辑是利用INSERT SELECT src.*直接将源视图整行数据插入目标表，未指定列名时，PostgreSQL会默认按目标表的列定义顺序填充，只要源视图和目标表结构对齐，就不会出现列不匹配问题。

动态生成列名（适配结构变更） #

如果要适配未来表结构的变更（比如新增列），避免每次改表都手动调整MERGE语句，可以通过PostgreSQL系统表动态生成列名列表，再拼接成动态SQL执行。

示例触发器函数（PL/pgSQL） #

CREATE OR REPLACE FUNCTION sync_my_table_trigger()
RETURNS TRIGGER AS $$
DECLARE
  target_cols text;
BEGIN
  -- 从系统表获取目标表的列名（按定义顺序）
  SELECT string_agg(quote_ident(column_name), ', ')
  INTO target_cols
  FROM information_schema.columns
  WHERE table_name = 'my_table'
    AND table_schema = current_schema()
  ORDER BY ordinal_position;

  -- 动态拼接并执行MERGE语句
  EXECUTE format('
    MERGE INTO my_table tgt
    USING my_view src
    ON tgt.business_unique_key = src.business_unique_key
    WHEN MATCHED THEN
      UPDATE SET id = src.id, cname = src.cname
    WHEN NOT MATCHED THEN
      INSERT (%s) SELECT %s FROM src;
  ', target_cols, target_cols);

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

说明 #

• quote_ident用于处理含特殊字符或关键字的列名，避免SQL语法错误。
• 动态生成的列名严格遵循目标表的定义顺序，确保和源视图列顺序一致。
• 如果是行级触发器（源表数据变更时触发同步），可在EXECUTE的USING子句传入触发行的匹配键，缩小源视图查询范围，提升性能。

版本升级提示 #

升级到PostgreSQL 14后，上述两种方案依然完全适用；PostgreSQL 15及以上版本对MERGE语法支持更灵活，但13/14的写法在升级后无需修改。

内容的提问来源于stack exchange，提问作者G-Man