Globally Unique Identifier in Vertica

Question

I am going to create some table at Vertica Database on which i have to give Global Unique Identifier to identify each row uniquely. Please suggest how to do it?

Answer 1

A named sequence is what you are looking for. You can use the same sequence for multiple tables:

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

Then NEXTVAL('named_sequence'), here used during table creation, is the equivalent of NEWID() you are looking for.

Answer 2

Please refer to the documentation:

Types of Incrementing Value Objects

• Named sequences are database objects that generate unique numbers in ascending or descending sequential order. They are most often used when an application requires a unique identifier in a table or an expression. Once a named sequence returns a value, it never returns that same value again. Named sequences are independent objects, and while you can use their values in tables, they are not subordinate to them.
• Auto-increment column value: a sequence available only for numeric column types. Auto-increment sequences automatically assign the next incremental sequence value for that column when a new row is added to the table.
• Identity column: a sequence available only for numeric column types.