When you want to have a unique identifier for a table, Vertica gives you 3 options. Create a numeric column either as AUTO_INCREMENT or IDENTITY, or use a sequence. The doc is comprehensive about the differences, but it can be summed up quite easily:

• IDENTITY and AUTO_INCREMENT are part of a table, a SEQUENCE is a standalone object
• AUTO_INCREMENT is simple and stupid and cannot be tweaked much, IDENTITY has a few more options (start value, increment) a SEQUENCE has a lot of options.

I will here talk a bit about the sequences, as they are the one allowing the most freedom.

Forcing the value of a incrementing column

If you have a table with an ‘id’ column, defined as a IDENTITY or AUTO_INCREMENT, you cannot set a value to this field during data load.:

CREATE TABLE tst_auto (id AUTO_INCREMENT, value varchar(10));
INSERT INTO tst_auto (id, value) VALUES (42, 'cheese');

ERROR 2444: Cannot insert into or update IDENTITY/AUTO_INCREMENT column "id"

If, on the other hand, you use a SEQUENCE, this is possible:

CREATE TABLE test_seq (id INT, value VARCHAR(10));
CREATE SEQUENCE seq;
ALTER TABLE test_seq ALTER COLUMN id set default NEXTVAL('seq');

You can then see that it does what you expect:

INSERT INTO test_seq (value) VALUES ('default');
INSERT INTO test_seq (id, value) VALUES (42, 'forced');
select * from test;

 id | value
----+---------
 1  | default
 42 | forced

If you use this, you must of course be careful that there are no duplication. If the example, you could for instance set the next value of the sequence to 43:

ALTER SEQUENCE seq RESTART WITH 43;

Using a sequence as a global identifier

The fun thing with a sequence is that it can be used on more than one table, thus giving you a global identifier, for instance:

-- 2 tables...
CREATE TABLE tst  (id INT, value varchar(10));
CREATE TABLE tst2 (id INT, value varchar(10));

-- 1 sequence...
CREATE SEQUENCE tst_seq;

-- ... said sequence is used by both tables
ALTER TABLE tst  ALTER COLUMN id set default NEXTVAL('tst_seq');
ALTER TABLE tst2 ALTER COLUMN id set default NEXTVAL('tst_seq');

-- testing...
INSERT INTO tst  (value) VALUES ('tst');
INSERT INTO tst2 (value) VALUES ('tst2');

-- success!
SELECT * FROM tst;
--  id |  value
-- ----+---------
--   1 | tst
-- (1 row)

SELECT * FROM tst2;
--  id |  value
-- ----+----------
--   2 | tst2
-- (1 row)