PostgreSQL批量upsert查询写法报错、EXCLUDED写法可正常执行问题求助

报错原因 #

1. 语法非法：PostgreSQL的ON CONFLICT DO UPDATE子句中，SET关键字后仅支持逗号分隔的列名=值赋值表达式，你写法中的{(item_name='roundnext1', qoh='500'),(item_name='roundnext2', qoh='500')}使用花括号包裹多组元组的结构完全不符合PG语法规范，无法被解析。
2. 逻辑错误：批量插入多条数据触发冲突时，硬编码多组固定赋值的写法无法和触发冲突的行一一对应，PG无法识别哪组赋值应该对应哪条冲突行。

正确批量UPSERT写法 #

核心规则：插入时把冲突后需要更新的字段值直接定义在VALUES的对应行中，冲突时通过EXCLUDED关键字（指代本次尝试插入的行数据）来取值更新原有冲突行，不需要额外定义多组赋值逻辑。

示例1：不同冲突行更新不同值 #

如果你需要item_id=1的冲突行更新为item_name='roundnext1', qoh='500'，item_id=2的冲突行更新为item_name='roundnext2', qoh='500'，写法如下：

INSERT INTO ingest.items (item_id, company_id, item_name, qoh, last_modified_date, create_date) 
VALUES 
  ('1', '1', 'roundnext1', '500', now(), now()), 
  ('2', '2', 'roundnext2', '500', now(), now()),
  ('3', '3', 'shirt', '100', now(), now()) 
ON CONFLICT ON CONSTRAINT item_constraint 
DO UPDATE SET 
  item_name = EXCLUDED.item_name, 
  qoh = EXCLUDED.qoh,
  last_modified_date = EXCLUDED.last_modified_date;

示例2：所有冲突行更新为相同固定值 #

如果所有触发冲突的行都需要更新为统一的固定值，直接在SET后写固定赋值即可：

INSERT INTO ingest.items (item_id, company_id, item_name, qoh, last_modified_date, create_date) 
VALUES 
  ('1', '1', 'shirt','100', now(), now()), 
  ('2', '2', 'shirt','100', now(), now()),
  ('3', '3', 'shirt','100', now(), now()) 
ON CONFLICT ON CONSTRAINT item_constraint 
DO UPDATE SET 
  qoh = '500',
  last_modified_date = now();

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