随手记一个 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;