Auto Increment Column

[Jbaskin]

Is there a way to create tables with a auto increment column like in MySQL and SQL Server?

[JimKnicely]

Vertica supports the auto-increment and identity column-constraints. auto_increment is pretty basic, it always starts with 1 and increments by 1. The identity constraint is more robust in that it takes a few extra parameters: seed, increment and cache, where seed = the starting value, increment is the value to add to the previous row value, and cache specifies the number of unique numbers to be pre-allocated and stored in memory for faster access.

Here is an auto_increment example:

Code: Select all

dbadmin=> create table emp_name (id auto_increment, name varchar(100));
CREATE TABLE
dbadmin=> insert into emp_name (name) values ('Jim');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into emp_name (name) values ('Jane');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into emp_name (name) values ('Peng');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from emp_name;
 id | name
----+------
  1 | Jim
  2 | Jane
  3 | Peng
(3 rows)

Here is an identity example:

Code: Select all

dbadmin=> drop table emp_name;
DROP TABLE
dbadmin=> create table emp_name (id identity (1, 10, 30), name varchar(100));
CREATE TABLE
dbadmin=> insert into emp_name (name) values ('Jim');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into emp_name (name) values ('Jane');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into emp_name (name) values ('Peng');
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from emp_name;
 id | name
----+------
  1 | Jim
 11 | Jane
 21 | Peng
(3 rows)

[rajasekhart]

Hi,

Thank you for giving an excellent description about Auto Increment Column.

I am having a doubt regarding this. If you take a new session after inserting a few number of rows into the table containg Auto Increment field, and if you also run few insert statements in the new session, then the sequence of the numbers is differing.
Could you please tell me the solution to maintain the same sequence in any session.

Eg:
create table emp_name (id auto_increment, name varchar(100));
--Below statements ran in session 1
insert into emp_name (name) values ('Janu');
insert into emp_name (name) values ('Smith');
insert into emp_name (name) values ('Kate');

select * from emp_name;
id name
1 Janu
2 Smith
3 Kate

Now take a new session (say session 2 ) and insert some more rows into the same table.

insert into emp_name (name) values ('vinslet');
insert into emp_name (name) values ('Jack');

select * from emp_name;

id name
1 Janu
2 Smith
3 Kate
250001 vinslet
250002 Jack

If you observe the id of Vinslet and Jack are skipped from the actual sequence and got some random sequence.

Why it is happening in that way and is there any possibility to get the single sequence of numbers.
Please help me.

Thanks in advance.
Rajasekhar

[id10t]

Hi, rajasekhart!

Why it is happening in that way...

Because goal of sequences to provide unique number for row record and not for counting or numbering. Vertica's identity value is never rolled back even if the transaction that tries to insert a value into the table is not committed (also you can insert data from different machines/nodes and Vertica - is sharing nothing i.e node1 doesn't know how many rows will insert node2) - that why it happening in that way.
Can I ask you some question: if you want delete some row do you want that DBMS will renumber/recount a column? And if you need to insert row between two rows (by identity number) - what behavior of DBMS do you expect? Why it so important to you that Vertica will save a order of insert?
See [Vertica Doc :: Using Named Sequences] - there you will also find answer why new session started from number - 250K

Could you please tell me the solution to maintain the same sequence in any session.

Named sequence:

Code: Select all

CREATE SEQUENCE my_seq START 100;     -- create named sequence
SELECT NEXTVAL('my_seq');             -- get next value
SELECT CURRVAL('my_seq');             -- get current value

[rajasekhart]

If you have a look at sql server, it maintains a sequence of numbers when identity(1,1) is used on a column.
so, i just asked you whether it can have the same possibility as in sql server.
How ever, thanks for your help.

Raj..

[id10t]

Hi Raj!

What is a condition of rows numbering? Enumerating - it is kind of ordering. Does table has some order?
Well defined problem is a half solution.
If order is by insertion time - create timestamp column and enumerate by this column. [example]
If order is by ORDER BY aColumn/s - create projection with appropriate order and enumerate rows on output.