postgresql with update returning 的隐藏问题

随手记一个 postgresql with 语法的小问题

当我们需要在一条语句中既做更新，有需要用更新的记录去插入（或更新其他数据时），需要使用到 with 语句，这个语句还能用来做 sql 的递归查询。一个例子如下：

WITH temp AS {
	UPDATE
		user u1
	SET
		id = uuid()
	FROM
		user u2
	WHERE
		u1.id = u2. parent_id
		AND ...
	RETURNING *
}
INSERT INTO B
SELECT id FROM temp；

这个例子是模拟了更新 user 表数据，然后将相关 id 插入 B 表；其中 update 时，在 user 表内做关联。

这个 sql 在执行时会报 column reference “id” is anbiguous 的错误，那么你能看出来是哪个 id 有问题么？

其实我在排查的时候一直以为是 update set 中的 id 报这个错误，其实是最后的 insert select 中的 id 报错。原因是在 with update 中进行了两表的关联，returning * 时会把关联起来的两张表视图返回，导致我们的 temp 中会有两个 id，以至于 insert select 中 id 不知道从哪个字段取值。

大家可以将 update 语句抽取出来单独执行以下，就能够恍然大悟了。

所以最终，我将 sql 改成类似于如下的情况RETURNING u1.*，这样 returning 的数据就不会模糊不清了😀：

WITH temp AS {
	UPDATE
		user u1
	SET
		id = uuid()
	FROM
		user u2
	WHERE
		u1.id = u2. parent_id
		AND ...
	RETURNING u1.*
}
INSERT INTO B
SELECT id FROM temp；