How to add IDENTITY/AUTO_INCREMENT in a existing table?

iddpanganibaniddpanganiban Community Edition User

We are moving data from the old server to the new server.

Answers

  • moshegmosheg Vertica Employee Administrator

    You cannot add IDENTITY/AUTO-INCREMENT columns to an existing table via the alter table command.
    Can you recreate the table with a new IDENTITY filed on the new server and load into it the data from the old server?

  • iddpanganibaniddpanganiban Community Edition User

    Hi Sir @mosheg ,

    I tried that but that we encountered an error because the id(auto_increment) should be same with old vertica server

    SQL Error [2444] [42601]: [Vertica]VJDBC ERROR: Cannot insert into or update IDENTITY/AUTO_INCREMENT column "pk_sales_id"
    [Vertica]VJDBC ERROR: Cannot insert into or update IDENTITY/AUTO_INCREMENT column "pk_sales_id"
    [Vertica]VJDBC ERROR: Cannot insert into or update IDENTITY/AUTO_INCREMENT column "pk_sales_id"

  • marcothesanemarcothesane - Select Field - Administrator

    There is one way of getting there
    Instead of

    CREATE TABLE foo (
      id IDENTITY NOT NULL
    , name VARCHAR(32)
    );
    

    Do:

    CREATE SEQUENCE foo_seq MINVALUE(<_higher value than the max value from source_>);
    CREATE TABLE foo (
      id INT NOT NULL DEFAULT foo_seq.nextval
    , name VARCHAR(32)
    );
    

    This way you can use the source identity value when you have it, and create your own for new data in the new table.

  • moshegmosheg Vertica Employee Administrator

    Consider this to get the max value online for the sequence

    CREATE TABLE t1 (f1 int, f2 varchar(20));
    INSERT INTO t1 values(10,'first value');
    INSERT INTO t1 values(20,'2nd value');
    COMMIT;
    
    \set STARTV `vsql -Atc "select max(f1) +1 from t1;"`
    CREATE SEQUENCE my_seq START :STARTV;
    CREATE TABLE t2 (f1 int DEFAULT NEXTVAL('my_seq'), f2 varchar(20));
    
    INSERT INTO t2
    SELECT * FROM t1 ORDER BY 1;
    
    INSERT INTO t2(f2) values('3rd value');
    INSERT INTO t2(f2) values('4th value');
    COMMIT;
    
    SELECT * FROM t1 order by 1;
      f1 |     f2
    ----+-------------
     10 | first value
     20 | 2nd value
    (2 rows)
    
    SELECT * FROM t2 order by 1;
      f1 |     f2
    ----+-------------
     10 | first value
     20 | 2nd value
     21 | 3rd value
     22 | 4th value
    (4 rows)
    
    
  • iddpanganibaniddpanganiban Community Edition User

    What will the effect and difference between in auto_increment and creating sequence will it affect the performance of our exiting etl?

  • marcothesanemarcothesane - Select Field - Administrator

    auto increment (that is: IDENTITY in Vertica syntax normally, creates a hidden, internal sequence - only visible to the table with the identity column. But the mechanics are exactly the same, whether it's a public sequence - created with CREATE SEQUENCE or a private one - created with an IDENTITY column

Leave a Comment

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