1

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.?

2
  • Don't use a row notation for a single column. set (varcol)=( excluded.varcol); should be set varcol=excluded.varcol; The expression varcol is a single column. The expression (varcol) is an anonymous record type with a single field.
    – user1822
    Commented Feb 7, 2022 at 11:05
  • @a_horse_with_no_name I believe the problem is not with the parenthese on the left side of the = sign, (varcol) = but with the right side: = (excluded.varcol). This does not parse as a row expression. If one uses set (varcol) = ROW(excluded.varcol), it should work fine. Commented Oct 25, 2022 at 15:17

1 Answer 1

2

The issue was with usage of brackets after "update set" , below query works without "select" requiring in update:

insert into    dest_t 
select * from source_t
on conflict on constraint unq_con_dest_t 
do update set varcol=excluded.varcol

Also pasting proper justification provided by @a_horse_with_no_name from his comment:

Don't use a row notation for a single column. set (varcol)=( excluded.varcol); should be set varcol=excluded.varcol; The expression varcol is a single column. The expression (varcol) is an anonymous record type with a single field

1
  • 1
    This can be used as well: set (varcol) = ROW(excluded.varcol) Commented Oct 25, 2022 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.