Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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);

Comments

  • Hi Sajan, we'll look into this and see if we can find a fix for you.
  • 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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.