Altering Sequences

The ALTER SEQUENCE statement lets you change the attributes of a previously-defined named sequence. Changes take effect in the next database session. Any parameters not specifically set in the ALTER SEQUENCE command retain their previous settings.

The ALTER SEQUENCE statement lets you rename an existing sequence, or the schema of a sequence, but you cannot combine either of these changes with any other optional parameters.

Note: Using ALTER SEQUENCE to set a START value below the CURRVAL can result in duplicate keys.

Examples

The following example modifies an ascending sequence called my_seq to start at 105:

=>ALTER SEQUENCE my_seq RESTART WITH 105;

The following example moves a sequence from one schema to another:

=>ALTER SEQUENCE [public.]my_seq SET SCHEMA vmart;

The following example renames a sequence in the Vmart schema:

=>ALTER SEQUENCE [vmart.]my_seq RENAME TO serial;

Remember that changes occur only after you start a new database session. For example, if you create a named sequence my_sequence, starting at value 10, each time you call NEXTVAL(), you increment the value by 1, as in the following series of commands:

=>CREATE SEQUENCE my_sequence START 10;
=>SELECT NEXTVAL('my_sequence');
 nextval
---------
      10
(1 row)
=>SELECT NEXTVAL('my_sequence');
 nextval
---------
      11
(1 row)

Next, issue the ALTER SEQUENCE statement to assign a new value starting at 50:

=>ALTER SEQUENCE my_sequence RESTART WITH 50;

When you call the NEXTVAL function, the sequence is incremented again by 1 value:

=>SELECT NEXTVAL('my_sequence');
 NEXTVAL
---------
      12
(1 row)

The sequence starts at 50 only after restarting the database session:

=>SELECT NEXTVAL('my_sequence');
 NEXTVAL
---------
      50
(1 row)