ALTER SEQUENCE
Alter attributes of an existing sequence. You can change the name of a sequence, its owner, or its schema. You can also restart a sequence.
ALTER SEQUENCE [ IF EXISTS ] name
RESTART [ [ WITH ] number ] |
START [ WITH ] number |
OWNER TO { new_owner | CURRENT_USER | SESSION_USER } |
RENAME TO new_name |
SET SCHEMA new_schema
You cannot run an ALTER SEQUENCE
command on a database that is in
HOT_STANDBY
mode.
- IF EXISTS
- Alter the sequence if it exists, but do not return an error message if it does not exist.
- name
- Name of the sequence to alter, optionally schema-qualified.
- RESTART
- Reset the starting value of a sequence:
- If you do not specify a
RESTART
number, and aSTART
value was previously defined for the sequence, it is restarted from that value. - If you do not specify a
RESTART
number, and noSTART
value was previously defined for the sequence, it is restarted from 1024. - If you specify a number, the sequence restarts from that value.
WITH
is an optional keyword.
- If you do not specify a
- START
- Set a new starting number for generated sequence values, such as
START 1000000
. When you alter a sequence, this value is a pending start value. The new start value takes effect only when you restart the sequence with theRESTART
option. Alternatively, you can provide the starting number in theRESTART
command itself. (Note that when you create a sequence, itsSTART
value takes effect immediately.) - OWNER TO
- Change the owner of the sequence. (See also CURRENT_USER and SESSION_USER.)
- RENAME TO
- Change the name of the sequence. This name cannot be schema-qualified.
- SET SCHEMA
- Change the schema that the sequence belongs to.
Examples
Rename a
sequence:
premdb=# alter sequence public.matchid rename to matchkey;
ALTER SEQUENCE
Restart the
sequence:
premdb=# alter sequence matchkey restart;
ALTER SEQUENCE
premdb=# select nextval('matchkey');
nextval
------------
5000001535
(1 row)
Alter the
START
value for the sequence and restart
it:premdb=# alter sequence matchkey start 10000;
ALTER SEQUENCE
premdb=# alter sequence matchkey restart;
ALTER SEQUENCE
premdb=# select nextval('matchkey');
nextval
---------
11263
(1 row)
Change the schema for a
sequence:
premdb=# alter sequence matchkey set schema bobr;
ALTER SEQUENCE
premdb=# alter sequence if exists matchid set schema bobr;
ALTER SEQUENCE