PostgreSQL含生成列表如何复制行且无需指定列名？

一、无需触发器的原生动态列方案 #

如果用的是PostgreSQL（生成列是PG12+支持的特性），可以借助系统视图自动筛选非生成列，避免手动写死列名，同时兼容后续新增列：

• 先动态获取表中所有非生成列的列表：

  SELECT string_agg(column_name, ', ')
  FROM information_schema.columns
  WHERE table_name = 'foo'
    AND (column_default IS NULL 
         OR column_default NOT LIKE '%GENERATED ALWAYS%');
  

• 把上述查询得到的列名，直接拼入插入语句：

  INSERT INTO foo (<这里填上面得到的列名>)
  SELECT <同样的列名> FROM foo;
  

• 要是想彻底自动化，还可以写个存储过程，每次调用时自动拉取最新的非生成列，新增列后完全不用修改插入逻辑。

二、触发器替代生成列的实操注意点 #

如果确实要换成触发器实现z的计算，这些细节要留意：

• 先创建计算z的函数和前置触发器：

  CREATE OR REPLACE FUNCTION set_foo_z()
  RETURNS TRIGGER AS $$
  BEGIN
    NEW.z := NEW.x + NEW.y;
    RETURN NEW;
  END;
  $$ LANGUAGE plpgsql;
  
  CREATE TRIGGER trigger_foo_set_z
  BEFORE INSERT OR UPDATE ON foo
  FOR EACH ROW EXECUTE FUNCTION set_foo_z();
  

• 优势：此时INSERT INTO foo SELECT * FROM foo;可以直接执行，因为z不再是生成列，触发器会自动覆盖它的值，无需手动忽略列；后续新增列后，只要触发器不影响新列，插入语句依然正常运行。
• 劣势：原生生成列的性能比触发器略优，且生成列是只读的（GENERATED ALWAYS），触发器模式下用户可以手动修改z，如果要保证数据一致性，得额外加CHECK(z = x + y)约束，或者在触发器里强制覆盖所有修改。

三、折中方案：保留生成列+视图封装 #

如果不想放弃生成列的原生特性，可以创建一个只包含非生成列的视图：

CREATE VIEW foo_insert AS
SELECT x, y FROM foo;

后续新增列时，只需要更新视图把新列加进去即可，插入时直接用视图：

INSERT INTO foo SELECT * FROM foo_insert;

这个方案兼顾了生成列的优势，缺点是新增列时需要手动更新视图，适合新增列不频繁的场景。

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