Merge on table having Identity Column

Hi,

Since the target table has an IDENTITY, you'll have to do the merge manually with and UPDATE statement followed by an INSERT statement.

Example:

dbadmin=> CREATE TABLE source_table (c1 INT, c2 VARCHAR(100));
CREATE TABLE

dbadmin=> CREATE TABLE target_table (c1 IDENTITY, c2 INT, c3 VARCHAR(100));
CREATE TABLE

dbadmin=> INSERT INTO source_table SELECT 1, 'TEST1';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO source_table SELECT 2, 'TEST2';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO source_table SELECT 3, 'TEST3';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO target_table (c2, c3) SELECT 1, 'TEST4';
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> SELECT * FROM source_table ORDER BY c1;
 c1 |  c2
----+-------
  1 | TEST1
  2 | TEST2
  3 | TEST3
(3 rows)

dbadmin=> SELECT * FROM target_table;
 c1 | c2 |  c3
----+----+-------
  1 |  1 | TEST4
(1 row)

dbadmin=> MERGE INTO target_table
dbadmin-> USING source_table
dbadmin->    ON source_table.c1 = target_table.c2
dbadmin->  WHEN MATCHED THEN UPDATE SET c3 = source_table.c2
dbadmin->  WHEN NOT MATCHED THEN INSERT (c2, c3) VALUES (source_table.c1, source_table.c2);
ERROR 4711:  Sequence or IDENTITY/AUTO_INCREMENT column in merge query is not supported
dbadmin=> Sequence or IDENTITY/AUTO_INCREMENT column in merge query is not supported

dbadmin=> UPDATE target_table SET c2 = source_table.c1, c3 = source_table.c2
dbadmin->   FROM source_table
dbadmin-> WHERE source_table.c1 = target_table.c1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO target_table (c2, c3)
dbadmin->   SELECT c1, c2
dbadmin->     FROM source_table
dbadmin->    WHERE NOT EXISTS (SELECT NULL
dbadmin(>                        FROM target_table
dbadmin(>                       WHERE target_table.c2 = source_table.c1);
 OUTPUT
--------
      2
(1 row)

dbadmin=> SELECT * FROM target_table ORDER BY 2;
 c1 | c2 |  c3
----+----+-------
  1 |  1 | TEST1
  3 |  2 | TEST2
  2 |  3 | TEST3
(3 rows)

So Does this means that vertica do not support merge insert for tables having identity column in it.?

Yes. That's why in my example I got the error message: "ERROR 4711: Sequence or IDENTITY/AUTO_INCREMENT column in merge query is not supported"

Hello
We get same error when the MERGE is on a table having a column with a DEFAULT VALUE based on a sequence .... (not defined as Identity) . We turned to merge to avoid problems with Updates , but now we hit another problem.... Is there any workaround since 2018 (date of last post here)
Thanks
Isabelle

Hi Isabelle -
Yes , this remains the same. DEFAULT sequencename.nextval has the same behaviour as an IDENTITY column.