PostgreSQL如何高效执行UPSERT操作_利用ON CONFLICT指令-SQL-PHP中文网

on conflict 是 postgresql 唯一可靠原子的 upsert 方式，必须基于唯一约束（unique/primary key），不可用普通索引或 not null；多约束需显式指定列名或约束名；do update 中 where 作用于原行，excluded 引用新值；returning 需用 queryrow/fetchone 获取更新后行。

postgresql如何高效执行upsert操作_利用on conflict指令

ON CONFLICT 是 PostgreSQL 中唯一可靠、原子、可预测的 UPSERT 实现方式；其他“先查后插/更”的业务层逻辑在并发下必然出错，别试。

ON CONFLICT 必须基于唯一约束，否则直接报错

你写 ON CONFLICT (email)，PostgreSQL 不会自动给你建索引。它只检查该列是否已有 UNIQUE 或 PRIMARY KEY 约束。没有就报这个错：

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

常见误操作：

对着普通 INDEX（非唯一）用 ON CONFLICT —— 不行
对着 NOT NULL 列用 —— 不行
表刚建好，忘了加 UNIQUE(email) 就跑 SQL —— 报错

验证方法：在 psql 里执行 \d 表名，确认目标列旁标着 UNIQUE 或 PK。

多唯一约束时，必须显式指定冲突目标

一张表有 email UNIQUE、phone UNIQUE、id PRIMARY KEY，你却只写 ON CONFLICT (id)，那 email 冲突时照样报错，不会 fallback 到其他约束。

正确做法是二选一：

Closers Copy

一款专注于营销与销售文案创作的 AI 写作工具，围绕广告、推广和转化场景提供文字生成与优化能力，帮助营销人员提高内容生产效率。

下载

用列名：如果冲突只可能来自主键，就写 ON CONFLICT (id)
用约束名：更安全，尤其当你需要响应 email 冲突时，先查约束名：SELECT conname FROM pg_constraint WHERE conrelid = 'users'::regclass AND contype = 'u';，再写 ON CONFLICT ON CONSTRAINT users_email_key
联合唯一约束同理：比如 UNIQUE(user_id, product_id)，就得写 ON CONFLICT (user_id, product_id)

DO UPDATE 里用 WHERE 条件要格外小心

很多人加 WHERE products.status = 'active' 是为了“只更新有效商品”，但这个 WHERE 是作用于原表行（即冲突行）的，不是作用于新数据。一旦条件不成立，整个 DO UPDATE 就被跳过——既没插入，也没更新，语句还返回成功（INSERT 0 1），非常隐蔽。

容易踩的坑：

忘记加 WHERE 导致误覆盖历史状态
加了 WHERE 却没意识到它可能让整条 UPSERT “静默失效”
在 DO UPDATE SET 里引用 EXCLUDED 字段时，误写成 VALUES()（那是 MySQL 的写法，PostgreSQL 不认）

正确写法示例：

INSERT INTO products (sku, price, status) 
VALUES ('IPHONE_15', 7999, 'active') 
ON CONFLICT (sku) 
DO UPDATE SET price = EXCLUDED.price, updated_at = NOW() 
WHERE products.status = 'active';