unable to update a single column in upsert: postgres



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



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2