- 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.
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:
How much to increment or decrement the sequence on each call to
||The range of valid integers.|
||The sequence value on its next call to NEXTVAL.|
||How many sequence numbers are pre-allocated and stored in memory for faster access.|
||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)
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)
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
=> ALTER SEQUENCE s1.my_seq RENAME TO s1.serial;
This statement moves sequence
s1.serial to schema
=> 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;
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.