Merge on table having Identity Column

kapil9186

I want to perform a merge insert onto a table which has Identity column. The merge will be based on another column within the target table but target table do have identity column in it.

Jim_Knicely

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)

kapil9186

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

Jim_Knicely

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"

Isabelle_Vela

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

marcothesane

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