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 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