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

Specifies to generate an informational message if an object already exists under the specified name. If you omit this option and the object exists, Vertica generates a ROLLBACK error message. In both cases, the object is not created.

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

For related information, see ON_ERROR_STOP.

[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

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.

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 (default)

Determines the minimum value a sequence can generate. If you omit this clause or specify NO MINVALUE, default values are used: 1 and ‑263‑1 for ascending and descending sequences, respectively.

MAXVALUE integer
NO MAXVALUE (default)

Determines the maximum value for the sequence. If you omit this clause or specify NO MAXVALUE, default values are used: 263‑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.
  • 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, see Sequence Caching in the Administrator's Guide.
CYCLE
NO CYCLE (default)

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