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 For related information, see ON_ERROR_STOP. |
[database.]schema
|
Database and schema. The default schema is |
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 |
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 ‑263‑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: 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 |
CACHE integer | NO CACHE
|
Specifies whether to cache unique sequence numbers on each node for faster access.
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:
|
Privileges
Non-superusers: CREATE privilege on the schema
Examples
See Creating and Using Named Sequences.