Generating Values with Sequences ​

A sequence is a database object that generates unique integer values. You can use sequences to uniquely identify rows in tables when no "real" data is available or appropriate for that purpose. For example, you can use a sequence to generate surrogate primary keys for a large fact table.

Sequences are not tied to specific tables or columns. When you create a sequence, you give it a name and optionally a schema. Once created, a sequence is available for use by any table column in the database. The two main uses for sequences are bulk loads and inserts, where a new sequence value can be generated for each row. You can also generate sequence values within queries and as part of a CTAS statement.

You generate new values in all cases by calling the NEXTVAL function. Each time NEXTVAL is called, the sequence advances to its next value.

Sequence values are generated independently on the worker nodes. Each value is stored as a 64-bit BIGINT number. The last 10 bits identify the worker. Increments of 1024 or greater are added to each new generated value to preserve the uniqueness of a specific sequence object across all of the worker nodes on the cluster. Each worker node generates its own distinct set of values.

Specific sequence values reported by system views and in query results depend on which node is the last one to finish executing the NEXTVAL function. Sequence values are highly timing-dependent; the increment step is not expected to be uniform between NEXTVAL calls.

If you want to reset the starting value for a sequence, you can use the ALTER SEQUENCE RESTART command. If you need to protect a sequence from being modified by other users and sessions, you can open a transaction and lock the sequence. Other transactions will not be able to modify the sequence until the transaction with the lock commits or is rolled back.

The lowest sequence number that is ever generated is 1024.

The manager node also generates its own set of sequence values for use in INSERT INTO...VALUES operations. These operations do not execute on the worker nodes.

Note: The maximum supported sequence value is 9223372036854775807 (263-1). When the maximum sequence value is reached on a node, the system returns an error. The database does not cycle through the range automatically; you may need to restart the sequence. Sequence values do not roll back when a transaction is rolled back. Those sequence values are considered used and cannot be reused.

Parent topic:Managing Tables and Views