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;
这种写法的优势很明显：
- 一次查询，多次复用：
oldCTE 只执行一次，其结果在后续的更新中被复用，避免了重复扫描。 - 便于扩展：如果需要根据条件批量交换多行数据，只需将筛选条件移到 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 来获取旧值，才是值得信赖的最佳实践。
