CREATE SEQUENCE

Defines a new named sequence number generator object. Like AUTO_INCREMENT and IDENTITY sequences, named sequences let you set the default values of primary key columns. Sequences guarantee uniqueness, and avoid constraint enforcement problems and overhead.

For more information about sequence types and their 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*

Identifies 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 [BY] integer

A 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 integer | NO MINVALUE

Determines the minimum value a sequence can generate. If you omit this clause or specify NO MINVALUE (the default), default values are used: 1 and -2⁶³-1 for ascending and descending sequences, respectively.

MAXVALUE integer | NO MAXVALUE

Determines the maximum value for the sequence. If you omit this clause or specify NO MAXVALUE (the default), default values are used: 2⁶³-1 and -1 for ascending and descending sequences, respectively.

START [WITH] integer

Sets the sequence start value to integer. The next call to NEXTVAL returns integer. If you omit this clause, the sequence start value is set to MINVALUE for ascending sequences, and MAXVALUE for descending sequences.

CACHE integer | NO CACHE

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

• >1 specifies how many unique numbers each node caches per session.

  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).

If you omit this clause, the sequence cache is set to 250,000.

For details on named sequence caching, see Distributing named sequences.

CYCLE | NO CYCLE

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

• CYCLE: The sequence wraps as follows:

  • When an incrementing sequence reaches its upper limit, it is reset to its minimum value.

  • When an decrementing sequence reaches its lower limit, it is reset to its maximum value.

• NO CYCLE (default): Calls to NEXTVAL return an error after the sequence reaches its maximum or minimum value.

Privileges

Non-superusers: CREATE privilege on the schema

Examples

See Creating and using named sequences.

See also

• ALTER SEQUENCE
• DROP SEQUENCE