Using SEQUENCE or IDENTITY as table column auto increment

Moderator: NorbertKrupa

Post Reply
User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

Using SEQUENCE or IDENTITY as table column auto increment

Post by Josh » Tue Jul 08, 2014 1:38 pm

I am curious about what others feel is the best solution for an auto incrementing column in a table. Is it better to use an identity or a sequence?

Sequence:

Code: Select all

dbadmin=> create sequence my_seq;
CREATE SEQUENCE
dbadmin=> create table test (t int not null default my_seq.nextval, t1 varchar(10));
CREATE TABLE
dbadmin=> drop table test;

dbadmin=> insert into test (t1) values ('test1');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test (t1) values ('test2');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from test;
 t |  t1
---+-------
 2 | test2
 1 | test1
(2 rows)
Identity:

Code: Select all

dbadmin=> drop table test;
DROP TABLE
dbadmin=> create table test (t identity, t1 varchar(10));
CREATE TABLE
dbadmin=> insert into test (t1) values ('test1');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test (t1) values ('test2');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from test;
 t |  t1
---+-------
 2 | test2
 1 | test1
(2 rows)
I'm leaning toward using a sequence.

Thanks for your input :)
Thank you!
Joshua

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Using SEQUENCE or IDENTITY as table column auto incremen

Post by NorbertKrupa » Tue Jul 08, 2014 3:03 pm

A sequence provides the most control over generating uniqueness. The documentation has an excellent comparison table on the various options.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “New to Vertica”