PostgreSQL中ON CONFLICT时如何整行更新customers表？

我有一个名为customers的表，包含id、name、age、weight、hight、blood、sex、last name、datetime列。希望插入数据前校验：当datetime值已存在时，直接覆盖整行现有数据。目前的代码需要逐个列写UPDATE SET语句，因列数较多过于繁琐，想找更简便的实现方法。

现有代码片段：

INSERT INTO customers (id,name,age,weight,hight,blood,sex,"last name",datetime)
VALUES('1','2','3','4','5','6','7','8','2022-12-30') 
ON CONFLICT (datetime) 
DO 
   UPDATE SET name = EXCLUDED.name || ';' || customers.name;
   ....
   UPDATE SET datetime = EXCLUDED.datetime || ';' || customers.datetime;

简便实现整行覆盖的方法 #

PostgreSQL支持直接通过表别名批量更新所有列，无需逐个列声明，分两种场景处理：

1. 覆盖除冲突列外的所有列 #

如果datetime是用于冲突检测的唯一约束列，且不需要修改该列本身，可使用以下写法：

INSERT INTO customers (id,name,age,weight,hight,blood,sex,"last name",datetime)
VALUES('1','2','3','4','5','6','7','8','2022-12-30') 
ON CONFLICT (datetime) 
DO UPDATE SET 
  customers = EXCLUDED,
  datetime = customers.datetime; -- 保留原datetime值，避免修改冲突列

2. 完全覆盖整行（包括冲突列） #

如果允许更新datetime列（需确保不会触发新的约束冲突），可简化为：

INSERT INTO customers (id,name,age,weight,hight,blood,sex,"last name",datetime)
VALUES('1','2','3','4','5','6','7','8','2022-12-30') 
ON CONFLICT (datetime) 
DO UPDATE SET customers = EXCLUDED;

注意事项 #

• 确保INSERT语句的列列表覆盖所有需要更新的字段，EXCLUDED对象会包含这些字段的新值。
• 如果表中有自增主键（比如id为SERIAL或IDENTITY类型），需单独保留原主键值：

  INSERT INTO customers (id,name,age,weight,hight,blood,sex,"last name",datetime)
  VALUES('1','2','3','4','5','6','7','8','2022-12-30') 
  ON CONFLICT (datetime) 
  DO UPDATE SET 
    customers = EXCLUDED,
    id = customers.id; -- 保留原主键值
  

• 包含空格的列名（如last name）需用双引号转义，避免语法错误。

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