CREATE SEQUENCE

Defines a new named sequence number generator object.

Use sequences or auto-incrementing columns for primary key columns. For example, to generate only even numbers in a sequence, specify a start value of 2, and increment the sequence by 2. Sequences guarantee uniqueness and avoid constraint enforcement problems and their associated overhead.

Syntax

CREATE SEQUENCE [schema.]sequence-name 
... [ INCREMENT [ BY ] positive-or-negative ]
... [ MINVALUE minvalue | NO MINVALUE ] 
... [ MAXVALUE maxvalue | NO MAXVALUE ]
... [ START [ WITH ] start ] 
... [ CACHE value | NO CACHE ] 
... [ CYCLE | NO CYCLE ]

Parameters

schema

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject
sequence-name

The name of the sequence to create, where sequence-name conforms to conventions described in Identifiers. It must also be unique among sequences, tables, projections, and views.

INCREMENT [BY] positive-or-negative

Specifies how much to increment (or decrement) the current sequence value. A positive value creates an ascending sequence; a negative value makes a descending sequence. The default value is 1.

MINVALUE minvalue | NO MINVALUE

Determines the minimum value a sequence can generate. If you do not specify this clause, or you specify NO MINVALUE, default values are used. The defaults are 1 and -263-1 for ascending and descending sequences, respectively.

MAXVALUE maxvalue | NO MAXVALUE

Determines the maximum value for the sequence. If this clause is not supplied or you specify NO MAXVALUE, default values are used. The defaults are 263-1 and -1 for ascending and descending sequences, respectively.

START [WITH] startvalue

Specifies a specific start value of the sequence (startvalue). The default values are minvalue for ascending sequences and maxvalue for descending sequences.

CACHE value | NO CACHE

Specifies how many sequence numbers are pre-allocated and stored in memory for faster access. The default is 250,000 with a minimum value of 1. The default cache size provides good efficiency for large insert or copy operations

Specifying a cache value of 1 indicates that only one value can be generated at a time, since no cache is assigned. Alternatively, you can specify NO CACHE.

Notes:

  • If you use the CACHE clause to create a sequence, each session has its own cache on each Vertica node.
  • Sequences that specify a cache size that is insufficient for the number of sequence values can cause performance degradation.
CYCLE | NO CYCLE

Specifies whether the sequence can wrap when its minimum or maximum values are reached:

  • CYCLE: Allows the sequence to wrap around when the maxvalue or minvalue is reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated is the minvalue or maxvalue, respectively.
  • NO CYCLE (default): Calls to NEXTVAL return an error after the sequence reaches its maximum/minimum value.

Privileges

To create a sequence, the user must have CREATE privilege on the schema to contain the sequence. Only the owner and superusers can initially access the sequence. All other users must be granted access to the sequence by a superuser or the owner.

To create a table with a sequence, the user must have SELECT privilege on the sequence and USAGE privilege on the schema that contains the sequence.

Note: Referencing a named sequence in a CREATE TABLE statement requires SELECT privilege on the sequence object and USAGE privilege on the schema of the named sequence.

Incrementing and Obtaining Sequence Values

After creating a sequence, use the NEXTVAL function to create a cache in which the sequence value is stored. Use the CURRVAL function to get the current sequence value.

You cannot use NEXTVAL or CURRVAL to act on a sequence in a SELECT statement:

Additionally, you cannot use NEXTVAL or CURRVAL to act on a sequence in:

You can use subqueries to work around some of these restrictions. For example, to use sequences with a DISTINCT clause:

=> SELECT t.col1, shift_allocation_seq.nextval   FROM (
     SELECT DISTINCT col1 FROM av_temp1) t;

Removing a Sequence

Use the DROP SEQUENCE function to remove a sequence. You cannot drop a sequence upon which other objects depend. Sequences used in a default expression of a column cannot be dropped until all references to the sequence are removed from the default expression.

DROP SEQUENCE ... CASCADE is not supported.

Examples

See Using Named Sequences in the Administrator's Guide.