ALTER SEQUENCE
Changes a named sequence in two ways:
- Sets parameters that control sequence behavior—for example, its start value, and range of minimum and maximum values. These changes take effect only when you start a new database session.
- Sets sequence name, schema, or ownership. These changes take effect immediately.
You can only modify a named sequence—that is, a sequence that was defined by CREATE SEQUENCE
. AUTO_INCREMENT and IDENTITY sequences are owned by the table where they were created, and cannot be changed independently of that table.
Syntax
ALTER SEQUENCE [[database.]schema.]sequence [ INCREMENT [ BY ] integer ] [ MINVALUE integer | NO MINVALUE ] [ MAXVALUE integer | NO MAXVALUE ] [ RESTART [ WITH ] integer ] [ CACHE integer | NO CACHE ] [ CYCLE | NO CYCLE ]
ALTER SEQUENCE [schema.]sequence-name { RENAME TO seq-name | SET SCHEMA schema-name] | OWNER TO owner-name }
Parameters
schema |
Specifies a schema, by default myschema.thisDbObject If you specify a database, it must be the current database. If you do not specify a schema, the table is created in the default schema. |
sequence |
The name of the sequence to alter. |
INCREMENT [BY] integer
|
A positive or negative integer that specifies how much to increment or decrement the sequence on each call to 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 (default)
|
Modifies the minimum value a sequence can generate. If you change this value and the current value exceeds the range, the current value is changed to the minimum value if increment is greater than zero, or to the maximum value if increment is less than zero. |
MAXVALUE integer
NO MAXVALUE (default)
|
Modifies the maximum value for the sequence. If you change this value and the current value exceeds the range, the current value is changed to the minimum value if increment is greater than zero, or to the maximum value if increment is less than zero. |
RESTART [WITH] integer |
Changes the current value of the sequence to integer. The next call to Using |
CACHE integerNO CACHE (default)
|
Specifies how many 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. By default, the sequence cache is set to 250,000. For details, see Distributing Named Sequences in the Administrator's Guide. |
CYCLE
NO CYCLE (default)
|
Specifies whether the sequence can wrap when its minimum or maximum values are reached:
|
RENAME TO seq‑name
|
Renames a sequence within the current schema, where seq‑name 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. |
SET SCHEMA schema‑name
|
Moves the sequence to schema schema‑name. |
OWNER TO owner‑name
|
Reassigns the current sequence owner to the specified owner. |
Privileges
Non-superuser: USAGE on the schema and one of the following:
- Sequence owner
- ALTER privilege on the sequence
For certain operations, non-superusers must have the following schema privileges:
Schema privileges required… | For these operations… |
---|---|
CREATE, USAGE | Rename sequence |
CREATE: destination schema |
Move sequence to another schema |
Examples
See Altering Sequences in the Administrator's Guide.