仅当子查询返回postgres结果时才更新表

Question

我有一个查询，它可能不会返回结果，这将导致值为NULL

update public.test set geom = 
(select geom from public.test where st_geometrytype(geom) = 'X' limit 1)

我试图添加COALESCE以替换为原始值，但给出错误

update public.ec_1_eur_1 set geom = 
COALESCE(select geom from public.ec_1_eur_1 where 
st_geometrytype(geom) = 'X' limit 1, geom)

此外，这也会产生错误

with s2 as (select geom from public.test where st_geometrytype(geom) = 'X' limit 1)
update public.test set geom = s2 where s2 is not null

Answer 1

我会在这里使用update/join语法，所以如果子查询不返回行，则不会更新任何内容：

update public.test t
set geom = t1.geom
from (
select geom 
from public.test 
where st_geometrytype(geom) = 'X' 
limit 1
) t1

对于您想要编写的查询，使用coalesce()：您需要将子查询用括号括起来，因此它明确地返回标量：

update public.test t
set geom = coalesce(
(select geom from public.test where st_geometrytype(geom) = 'X' limit 1),
geom
)

然而，这种方法效率较低，因为如果子查询不返回行，它仍然会将表中的所有行更新回其原始值；在这方面，更新/联接方法是优选的。

然而，请注意，没有order by的limit没有什么意义，因为它不是确定性的；当子查询生成几行时，将拾取哪一行是未定义的。