Skip to content

Fixing set-returning functions are not allowed in UPDATE

After upgrading to Postgres 12 statement

update temprid set
ContactFirstName =unnest(xpath(
yhik =unnest(xpath(
     from t

throws error

set-returning functions are not allowed in UPDATE

How to fix this ? In Postgres 9.1 it worked. I read similar answers here but they recomment to totally overwrite statement. Maybe there is simply change which makes this statement work ?

Result should by any value of xpath expression in case if xpath returns multiple values


I tried according to answer

update temprid set
ContactFirstName =xpath(

But got error

ERROR:  syntax error at or near "["
LINE 3: .../BuyerParty/ContactData/ContactFirstName/text()',x)[1]::text



Simply replace

SET col = unnest(array_value)


SET col = array_value[1]

In your case, you have to use an extra pair of parentheses, because the array_value is an expression:

SET ContactFirstName =
7 People found this is helpful