How to add IDENTITY/AUTO_INCREMENT in a existing table?

iddpanganiban

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

mosheg

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?

iddpanganiban

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"

marcothesane

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.

mosheg

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)

iddpanganiban

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

marcothesane

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