Merge doesn't work in table with sequence even if the sequence column is not part of merge

Sajan_Sangraula

According to the Vertica documentation, "If you are using named sequences, Vertica can perform a MERGE operation if you omit the sequence from the query. For example, if you define column c1 as follows, Vertica can do a merge.". However, even when I omit the sequenced column from my merge operation, I get an error "ERROR 4711: Sequence or IDENTITY/AUTO_INCREMENT column in merge query is not supported". Here are steps to replicate the issue: create sequence test_seq; create table test (id int primary key default nextval('xcx_analytics.test_seq'), name varchar(10)); insert into test (name) select 'apn' union select 'rat' union select 'imsi' union select 'imei'; create table test2 (id int, name varchar(10)); insert into test2 (id,name) dbadmin-> select 4,'apn' union select 5,'imei_tac' union select 6,'msisdn' union select 1,'imei'; merge into test t using test2 s on t.name=s.name when matched then update set name=s.name when not matched then insert (name) values (s.name);

[Deleted User]

Hi Sajan, we'll look into this and see if we can find a fix for you.

[Deleted User]

Hi Sajan: Apologies for the delay in getting back to you. I have verified the MERGE behavior with our developer. As the documentation notes, you cannot reference a sequence in a MERGE statement, either explicitly or implicitly. However, in your example, the statement attempts to create the test.id value implicitly from a sequence, which is not supported in a MERGE statement. The current workaround is to use another column as s.c1 in your example.