Altering Sequences
ALTER SEQUENCE
can change a named sequence in two ways:
- Reset parameters that control sequence behavior—for example, its start value, or range of minimum and maximum values. These changes take effect only when you start a new database session.
- Reset sequence name, schema, or ownership. These changes take effect immediately.
Note: The same ALTER SEQUENCE
statement cannot make both types of changes.
Changing Sequence Behavior
ALTER SEQUENCE
can change one or more sequence attributes through the following parameters:
These parameters... | Control... |
---|---|
INCREMENT
|
How much to increment or decrement the sequence on each call to |
MINVALUE /MAXVALUE |
The range of valid integers. |
RESTART
|
The sequence value on its next call to NEXTVAL. |
CACHE /NO CACHE |
How many sequence numbers are pre-allocated and stored in memory for faster access. |
CYCLE /NO CYCLE |
Whether the sequence wraps when its minimum or maximum values are reached. |
These changes take effect only when you start a new database session. For example, if you create a named sequence my_sequence
that starts at 10 and increments by 1 (the default), each sequence call to NEXTVAL
increments its value by 1:
=> CREATE SEQUENCE my_sequence START 10; => SELECT NEXTVAL('my_sequence'); nextval --------- 10 (1 row) => SELECT NEXTVAL('my_sequence'); nextval --------- 11 (1 row)
The following ALTER SEQUENCE
statement specifies to restart the sequence at 50:
=>ALTER SEQUENCE my_sequence RESTART WITH 50;
However, this change has no effect in the current session. The next call to NEXTVAL
increments the sequence to 12:
=> SELECT NEXTVAL('my_sequence'); NEXTVAL --------- 12 (1 row)
The sequence restarts at 50 only after you start a new database session:
=> \q $ vsql Welcome to vsql, the Vertica Analytic Database interactive terminal. => SELECT NEXTVAL('my_sequence'); NEXTVAL --------- 50 (1 row)
Changing Sequence Name, Schema, and Ownership
You can use ALTER SEQUENCE
to make the following changes to a named sequence:
- Rename it.
- Move it to another schema.
- Reassign ownership.
Each of these changes requires separate ALTER SEQUENCE
statements. These changes take effect immediately.
For example, the following statement renames a sequence from my_seq
to serial
:
=> ALTER SEQUENCE s1.my_seq RENAME TO s1.serial;
This statement moves sequence s1.serial
to schema s2
:
=> ALTER SEQUENCE s1.my_seq SET SCHEMA TO s2;
The following statement reassigns ownership of s2.serial
to another user:
=> ALTER SEQUENCE s2.serial OWNER TO bertie;
Note: Only a superuser or the sequence owner can change its ownership. Reassignment does not transfer grants from the original owner to the new owner. Grants made by the original owner are dropped.