1

I'm trying to replace Postgres's "on conflict (..) do update.." with a merge statement on an H2 database, I'm however facing an issue I'm unable to figure out.

create table shop (
  name varchar(40) NOT NULL,
  block_nbr bigint DEFAULT 0,
  is_open boolean DEFAULT false,
  PRIMARY KEY (name)
);

Then I try to execute the following merge statement:

MERGE INTO shop USING VALUES('shopname', 3) S(name, block_nbr)
    ON shop.name = S.name
    WHEN MATCHED THEN UPDATE SET name=S.name, block_nbr=S.block_nbr
    WHEN NOT MATCHED THEN INSERT VALUES (S.name, S.block_nbr);

The above query doesn't work, it outputs a "Column count does not match" error. The column names and value count are the same, this error usually happens in insert statements when the number of columns name is not the same as the values given.

I tried to modify the above query by providing the extra value missing but without specifying its corresponding column and it worked but I don't want to insert that value.

MERGE INTO shop USING VALUES('shopname', 3) S(name, block_nbr)
    ON shop.name = S.name
    WHEN MATCHED THEN UPDATE SET name=S.name, block_nbr=S.block_nbr
    WHEN NOT MATCHED THEN INSERT VALUES (S.name, S.block_nbr, true);
Aud12
  • 23
  • 1
  • 4

1 Answers1

0

It's the INSERT part that fails because the table has 3 columns, but you are only providing 2 values.

MERGE INTO shop 
USING VALUES ('shopname', 3) s(name, block_nbr)
 ON shop.name = s.name
    WHEN MATCHED THEN UPDATE SET name=S.name, bloc_nbr=S.bloc_nbr
    WHEN NOT MATCHED THEN INSERT (name, block_nbr) VALUES (S.name, S.block_nbr)
;
  • I know that, any idea how to solve it without providing the third value? It works with Postgres's feature "on conflict do update", I just want to achieve that same behavior, also the block_nbr is only a typo in this post but not on my code. – Aud12 Nov 11 '22 at 20:10
  • @Aud12: did you read my answer? I am not providing a third value. –  Nov 11 '22 at 20:11
  • Just noticed the last change you did in the query, thank you, it worked! – Aud12 Nov 11 '22 at 20:16