update table only if subquery returns the result postgres

Question

I have query which might not return result which will cause NULL value

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

I tried to add COALESCE to replace with original value but gives error

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)

Also this also gives error

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

I would use the update/join syntax here, so nothing is updated if the subquery returns no row:

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

As for the query you wanted to write, using coalesce(): you would need to surround the subquery with parentheses, so it is explicit that it returns a scalar:

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

However this approach is less efficient, because it still updates all rows of the table back to their original value if the subquery returns no rows; the update/join approach is preferrable in that regard.

Note, however, that limit without order by makes little sense, because it is not deterministic; when the subquery produces several rows, it is undefined which one will be picked.