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.?
set (varcol)=( excluded.varcol);
should beset varcol=excluded.varcol;
The expressionvarcol
is a single column. The expression(varcol)
is an anonymous record type with a single field.(varcol) =
but with the right side:= (excluded.varcol)
. This does not parse as a row expression. If one usesset (varcol) = ROW(excluded.varcol)
, it should work fine.