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
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 a START value was previously defined for the sequence, it is restarted from that value.
  • If you do not specify a RESTART number, and no START 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.
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 the RESTART option. Alternatively, you can provide the starting number in the RESTART command itself. (Note that when you create a sequence, its START 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