CREATE SEQUENCE

Defines a named sequence number generator object. Named sequences let you set the default values of primary key columns. Sequences guarantee uniqueness, and avoid constraint enforcement issues.

For more information about sequence types and usage, see Sequences.

Syntax

CREATE SEQUENCE [ IF NOT EXISTS ] [[database.]schema.]sequence
   [ INCREMENT [ BY ] integer ]
   [ MINVALUE integer | NO MINVALUE ]
   [ MAXVALUE maxvalue | NO MAXVALUE ]
   [ START [ WITH ] integer ]
   [ CACHE integer | NO CACHE ]
   [ CYCLE | NO CYCLE ]

Parameters

IF NOT EXISTS

If an object with the same name exists, do not create it and proceed. If you omit this option and the object exists, Vertica generates a ROLLBACK error message. In both cases, the object is not created if it already exists.

The IF NOT EXISTS clause is useful for SQL scripts where you want to create an object if it does not already exist.

For related information, see ON_ERROR_STOP.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

sequence

Name of the sequence to create, where sequence conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

INCREMENT

Positive or negative integer that specifies how much to increment or decrement the sequence on each call to NEXTVAL, by default set to 1.

Note

Setting this parameter to integer guarantees that column values always increment by at least integer. However, column values can sometimes increment by more than integer unless you also set the NO CACHE parameter.

MINVALUE|NO MINVALUE

Minimum integer value of the sequence. If omitted, the following defaults apply:

• Ascending sequence: 1

• Descending sequence: -2⁶³

MAXVALUE|NO MAXVALUE

Maximum integer value of the sequence. If omitted, the following defaults apply:

• Ascending sequence: 2⁶³

• Descending sequence: -1

START

Integer start value of the sequence. The next call to NEXTVAL returns the start value. If omitted, the following defaults apply:

• Ascending sequence: MINVALUE

• Descending sequence: MAXVALUE

CACHE|NO CACHE

Whether to cache unique sequence numbers on each node for faster access. CACHE takes an integer argument as follows:

• >1 specifies how many unique sequence numbers are pre-allocated and stored in memory for faster access. Vertica sets up caching for each session, and distributes it across all nodes.

  Caution

  If sequence caching is set to a low number, nodes are liable to request a new set of cache values more frequently. While it supplies a new cache, Vertica must lock the catalog. Until Vertica releases the lock, other database activities such as table inserts are blocked, which can adversely affect overall performance.

• 0 or 1 specifies to disable caching (equivalent to NO CACHE).

By default, the sequence cache is set to 250,000.

For details, see Distributing sequences.

CYCLE|NO CYCLE

Whether the sequence wraps:

• CYCLE:

  • Incrementing sequence: On reaching MAXVALUE, wraps to MINVALUE.

  • Decrementing sequence: On reaching MINVALUE, wraps to MAXVALUE.

• NO CYCLE (default): Calls to NEXTVAL return an error after the sequence reaches its upper or lower limit.

Privileges

Non-superusers: CREATE privilege on the schema

Examples

See Creating and using named sequences.

See also

• ALTER SEQUENCE
• DROP SEQUENCE