PostgreSQL中UPDATE不能直接用SET a = b, b = a的原因

直接写 SET a = b, b = a 就能交换两个字段的值？这个直觉很自然，但结果往往会让人意外——两个字段最终都会变成原 b 的值，交换操作失败了。问题出在 PostgreSQL 对 SET 子句的求值机制上：所有右侧表达式在更新开始前就已统一计算完毕。你可以把它想象成，数据库先为当前行拍了一张“快照”，读取了所有旧值，然后再根据这张快照里的值进行写入。因此，当它计算 a = b 和 b = a 时，右侧引用的 b 和 a 都来自同一份快照，最终导致两个字段都被赋予了快照里 b 的初始值。

免费影视、动漫、音乐、游戏、小说资源长期稳定更新！ 👉 点此立即查看 👈

用ROW() + VALUES()实现真正原子交换

那么，如何正确实现交换呢？PostgreSQL 提供了一个优雅的特性：元组（tuple）级别的赋值。它允许你将多个列打包成一个整体进行读取和写入，从而巧妙地绕过了逐列求值的限制。核心语法是利用 VALUES 构造一个行值，再用 ROW() 进行解构赋值。来看一个典型的例子：

UPDATE accounts
SET (balance, overdraft_limit) = (SELECT overdraft_limit, balance FROM accounts WHERE id = 1)
WHERE id = 1;

这种写法虽然有效，但有三个关键细节必须注意：

• 必须使用子查询：直接引用列名会触发 ERROR: cannot use column references in VALUES 错误，所以需要用子查询包裹起来。
• 确保子查询返回单行：目标行的筛选条件必须精确，否则会因返回多行而报错。
• 字段类型需兼容：交换的字段在数量和数据类型上必须严格匹配。例如，numeric 和 integer 通常可以隐式转换，但像 text 和 jsonb 这类差异较大的类型则不行。

用CTE避免子查询重复扫描（大表必备）

上面基于子查询的方法，对于小表来说没问题。但如果面对的是大表，它可能会对同一行数据扫描两次：一次用于获取旧值，另一次执行更新。为了提升效率并使语义更清晰，我们可以借助公共表表达式（CTE）：

WITH old AS (
  SELECT balance, overdraft_limit FROM accounts WHERE id = 1
)
UPDATE accounts
SET (balance, overdraft_limit) = (SELECT overdraft_limit, balance FROM old)
WHERE id = 1;

这种写法的优势很明显：

• 一次查询，多次复用：old CTE 只执行一次，其结果在后续的更新中被复用，避免了重复扫描。
• 便于扩展：如果需要根据条件批量交换多行数据，只需将筛选条件移到 CTE 内部，逻辑会更安全、更清晰。
• 关于物化：在 PostgreSQL 中，CTE 默认是物化的（除非显式使用 MATERIALIZED 控制），但这并不影响我们这里交换操作的正确性。

别踩UPDATE ... FROM的坑

或许你会想到另一种思路：用 UPDATE ... FROM 语法来实现自表交换，比如：

UPDATE accounts a
SET (balance, overdraft_limit) = (b.overdraft_limit, b.balance)
FROM accounts b
WHERE a.id = b.id AND a.id = 1;

这个写法看起来逻辑自洽，但强烈不推荐在实际生产中使用。原因在于其行为不可靠：

• 快照一致性无保证：PostgreSQL 并不保证 FROM 子句中的表别名 b 读取的一定是更新前的数据快照。在某些版本或特定的隔离级别下，它有可能读到已更新的中间状态。
• 官方明确警示：文档指出，UPDATE ... FROM 中的源表不被视为“快照一致性读”。它设计用于从其他关联表取值，而非用于处理同一张表内的数据交换。
• 潜在风险：在测试中，这种写法曾导致交换后两字段值相同，或在涉及唯一约束时引发冲突等非预期结果。

说到底，要实现真正安全的字段交换，必须确保读取源是稳定且明确的。元组赋值本身是原子的，但读取操作的稳定性决定了整个交换的可靠性。因此，依赖显式的子查询或 CTE 来获取旧值，才是值得信赖的最佳实践。

相关攻略

职业与学业

我的知心朋友

我的知心朋友 “猪猪！”伴随着这声专有称呼，我总爱扑到她面前，顺手捏捏那张胖嘟嘟的脸。回应我的，是一串同样搞怪的叫声。这个在座位上和我打打闹闹的小胖妞，就是我的初中好友——丛思琦。在班里女生中，她体积最大，用某位男生的话说，简直是“整个一猪”。但有趣的是，即便旁人以此打趣，她也从未因此露出半分不快。

热心网友

05.04

职业与学业

我的“开心果”朋友

我的“开心果”朋友 要说我们班女同学公认的“开心果”，那非陈宇婷莫属。你看她，眼睛小小的，一笑起来就眯成两条缝，配上一个大大的鼻子、淡淡的眉毛，还有那几粒俏皮的“小痘痘”，一张嘴巴总是红润润的，再加上一个可爱的双下巴，一看就是个健康又乐天的女孩。 她的“开心果”特质，在课间时分展现得淋漓尽致。总爱在

热心网友

05.04

职业与学业

我眼中的同学

我眼中的杨喆瑞 提起我们班的杨喆瑞，大家脑海里大概会立刻蹦出几个词：活泼、可爱，还带着点小淘气。没错，他就是这么一个小帅哥。一双眼睛又大又圆，特别有神，配上那张小小的嘴巴，整个人显得机灵极了。要说共同点，我俩大概是全班最爱往操场跑的孩子了，运动是我们的共同语言。至于学习嘛，他算不上拔尖，但身上有股劲

热心网友

05.04

职业与学业

快乐男孩

HI！我是一个快乐的小男孩 这个小男孩，外貌嘛，还算有点帅气：椭圆的脸蛋，配上一双明亮的眼睛，最显眼的还得数那两颗标志性的大“兔牙”。 要说最大的特点，那肯定是爱看书。每次一踏进书店，没有两三个小时，根本别想看到他出来。要不是妈妈过来“抓人”，他真恨不得在里面赖上一整天。难怪妈妈总说他是个不折不扣的

热心网友

05.04

职业与学业

老姐的小档案

姓名：雷颖 年龄：12岁 特点：手巧、爱玩电脑、爱吃甜点。 职业：小学生、小区提醒员。 今天，咱们就来聊聊我那位聪明又可爱的表姐，把她正式介绍给大家。说起她，那可真是一位“宝藏”女孩。 家里的“艺术家” 首先，老姐是我们家公认的艺术家，对手工制作情有独钟。还记得我第一次去她家玩，刚走到她房间门口，眼

热心网友

05.04

热门推荐

电脑教程

wf-1000xm4蓝牙配对需要按哪个键？

WF-1000XM4蓝牙配对指南：两种触发路径，一个核心逻辑 给索尼WF-1000XM4配对，核心其实就一件事：让耳机进入“被发现”的状态。有意思的是，它并不依赖某个单一的物理按键，而是提供了双路径的触发方式。根据官方的操作指南以及多次的实际测试，无论是通过充电盒上的功能键，还是直接操作耳机本身，都

热心网友

05.04

电脑教程

迅捷路由器桥接教程详细常见失败原因有哪些？

迅捷路由器桥接失败怎么办？原因分析与解决方法大全 许多用户在使用迅捷路由器进行无线桥接时，经常遇到“显示已连接但无法访问互联网”的问题。实际上，这通常并非设备故障，而是由于关键的网络参数配置不当或主副路由器之间的通信协调不畅所致。简单来说，就是两台路由器之间的设置没有完全匹配。那么，具体哪些环节最容

热心网友

05.04

电脑教程

迅捷路由器桥接教程详细包括手机设置吗？

迅捷路由器无线桥接：手机端设置实操指南 使用手机为迅捷路由器配置无线桥接（WDS），听似专业，实则通过官方适配的移动端界面就能轻松完成。只要满足几个关键条件，您仅需一部手机即可高效架设扩展网络。操作时，请先将手机连接至副路由器的默认无线信号（通常以FAST_XXXX格式命名），随后在Safari或C

热心网友

05.04

电脑教程

小米空调联网失败怎么办？

小米空调联网故障全解析：从新手排查到专家级修复，步步为营 当小米空调始终无法成功连接网络时，许多用户的第一反应往往是联系售后或怀疑设备故障。然而实际情况是，超过九成的联网失败案例，根源都出在网络配置、操作流程这类“软性”环节，空调硬件本身出问题的概率极低。解决问题的核心在于掌握系统化的排查思路，按照

热心网友

05.04

电脑教程

有线音响改无线蓝牙连接麻烦吗？

有线音响加装蓝牙功能并不复杂，普通用户借助外置蓝牙接收器即可在十分钟内完成升级 想给家里的老款有线音响“剪掉”那根烦人的音频线？其实这件事没你想的那么复杂。普通用户完全不需要动用电烙铁，借助一个小巧的外置蓝牙接收器，十分钟之内就能搞定升级。核心操作很简单：确认你的音箱背面有标准的3 5毫米或RCA音

热心网友

05.04