Working with Sequence Types

Vertica supports these types of incrementing values:

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.