Using AUTO_INCREMENT and IDENTITY Sequences
You can define a column that automatically increments its value as new rows are added, with options AUTO_INCREMENT
or IDENTITY
. See CREATE TABLE
Column-Constraint for syntax options.
While sequence object values are guaranteed to be unique, they are not guaranteed to be contiguous. Since sequences are not necessarily contiguous, you may interpret the returned values as missing. For example, two nodes can increment a sequence at different rates. The node with a heavier processing load increments the sequence, but the values are not contiguous with those being incremented on a node with less processing.
IDENTITY
and AUTO_INCREMENT
can take between 0 and three arguments, which Vertica parses as follows:
# arguments | Description |
---|---|
None |
Specifies starting column values at 1. Values increment by 1, but values are not guaranteed to be contiguous. Your number of nodes and cache impact sequence values. Setting cache to 1 (no cache) can ensure contiguous values. |
1 |
Specifies cache size, an integer value ≥1 that determines how many unique numbers each node is allocated per session: IDENTITY(cache)
Default: 250,000 |
2 or 3 |
Set as follows: IDENTITY (start, increment[, cache)] )
|
Note: The increment value is the minimum increment. For example, setting an increment value of 3 guarantees that values returned increment by at least 3. However, the values returned may not be contiguous, unless you have set the cache value to 1 (no cache).
The following restrictions apply to IDENTITY
and AUTO_INCREMENT
sequences:
- You can set this constraint on only one table column.
IDENTITY
andAUTO_INCREMENT
values are never rolled back, even if a transaction that tries to insert a value into a table is not committed.- You cannot change the value of an
IDENTITY
orAUTO_INCREMENT
column in an existing table.
Examples
The following example shows how you can use the IDENTITY
column-constraint to create a table with an ID column. The ID column has an initial value of 1. It is incremented by 1 every time a row is inserted.
=> CREATE TABLE Premium_Customer(
ID IDENTITY
(1,1),
lname VARCHAR(25),
fname VARCHAR(25),
store_membership_card INTEGER
);
=> INSERT INTO Premium_Customer (lname, fname, store_membership_card )
VALUES ('Gupta', 'Saleem', 475987);
Confirm the row you added and see the ID value:
=> SELECT * FROM Premium_Customer; ID | lname | fname | store_membership_card ----+-------+--------+----------------------- 1 | Gupta | Saleem | 475987 (1 row)
Now add another row:
=> INSERT INTO Premium_Customer (lname, fname, store_membership_card) VALUES ('Lee', 'Chen', 598742);
Calling the LAST_INSERT_ID function returns value 2 because you previously inserted a new customer (Chen Lee), and this value is incremented each time a row is inserted:
=> SELECT LAST_INSERT_ID(); last_insert_id ---------------- 2 (1 row)
View all the ID values in the Premium_Customer
table:
=> SELECT * FROM Premium_Customer; ID | lname | fname | store_membership_card ----+-------+--------+----------------------- 1 | Gupta | Saleem | 475987 2 | Lee | Chen | 598742 (2 rows)
The next examples illustrate the three valid ways to use IDENTITY
arguments. (Note that these examples are valid for the AUTO_INCREMENT
argument also.) The first example uses a cache of 100, and the defaults for start value (1) and increment value (1):
=> CREATE TABLE t1(x IDENTITY
(100), y INT);
The second example specifies the start and increment values as 1, and defaults to a cache value of 250,000:
=> CREATE TABLE t2(y IDENTITY
(1,1), x INT);
The third example specifies start and increment values of 1, and a cache value of 100:
=> CREATE TABLE t3(z IDENTITY
(1,1,100), zx INT);