Working with Sequence Types
Vertica supports these types of incrementing values:
- Auto-increment or Identity column: The Auto-increment and Identity types are identical. They are basic incrementing numeric column types that the database increments automatically.
- Named sequences: A named sequence is a database object that generates unique numbers in ascending or descending sequential order.
You define Auto-increment and Identity sequences through column constraints in the CREATE TABLE statement. These sequences are incremented each time a row is added to the table. Both of these sequence object types are table-dependent and do not persist independently. Vertica does not roll back an identity value even if a transaction that tries to insert a value is not committed. The LAST_INSERT_ID function returns the last value generated for an auto-increment or identity column.
Each type of sequence value has a set of properties. A named sequence has the most properties, and an Auto-increment sequence the least. The following table lists the differences between the three sequence values:
Behavior | Named Sequence | Identity | Auto-increment |
---|---|---|---|
Default cache value 250K |
X |
X |
X |
Set initial cache |
X |
X |
X |
Define start value |
X |
X |
X |
Specify increment unit |
X |
X |
X |
Exists as an independent object |
X |
||
Exists only as part of table |
X |
X |
|
Create as column constraint |
X |
X |
|
Requires name |
X |
||
Use in expressions |
X |
||
Unique across tables |
X |
||
Change parameters |
X |
||
Move to different schema |
X |
||
Set to increment or decrement |
X |
||
Grant privileges to object |
X |
||
Specify minimum value |
X |
||
Specify maximum value |
X |
Note: 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.