I came across a scenario where I would be trying to update a single column using UPSERT statement. But I get the error:
ERROR: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression
you can use below snippet to reproduce the issue:
drop table if exists source_t; create table source_t as (select 1 id, 'dummy1' varcol union select 3,'dummy3'); drop table if exists dest_t; create table dest_t as (select 1 id, 'mmy' varcol union select 2,'dummy2'); ALTER TABLE dest_t ADD CONSTRAINT UNQ_CON_dest_t unique (id); /***Below does not work***/ insert into dest_t select * from source_t on conflict on constraint unq_con_dest_t do update set (varcol)=( excluded.varcol); /***Below is working***/ insert into dest_t select * from source_t on conflict on constraint unq_con_dest_t do update set (varcol)=(select excluded.varcol); /**** Also if the table had another column and if I mentioned it in update , then "select " clause would not be necessary as well***/
Concluding, if the update clause has more than one column it is not necessary to use the "select " clause. Has anyone experienced the same issue by any chance? Is it something known thing/bug in postgres.?