ALTER SEQUENCE v15

Name

ALTER SEQUENCE — Change the definition of a sequence generator.

Synopsis

ALTER SEQUENCE <name> [ INCREMENT BY <increment> ]
  [ MINVALUE <minvalue> ] [ MAXVALUE <maxvalue> ]
  [ CACHE <cache> | NOCACHE ] [ CYCLE ]

Description

ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameter not specifically set in the ALTER SEQUENCE command retains its prior setting.

Parameters

name

The name (optionally schema-qualified) of a sequence to alter.

increment

The clause INCREMENT BY increment is optional. A positive value makes an ascending sequence, a negative one a descending sequence. If unspecified, the old increment value is maintained.

minvalue

The optional clause MINVALUE minvalue determines the minimum value a sequence can generate. If not specified, the current minimum value is maintained. You can use the keywords NO MINVALUE to set this behavior back to the defaults of 1 and -2⁶³-1 for ascending and descending sequences, respectively. However, this term isn't compatible with Oracle databases.

maxvalue

The optional clause MAXVALUE maxvalue determines the maximum value for the sequence. If not specified, the current maximum value is maintained. You can use the keywords NO MAXVALUE to set this behavior back to the defaults of 2⁶³-1 and -1 for ascending and descending sequences, respectively. However, this term isn't compatible with Oracle databases.

cache

The optional clause CACHE cache specifies how many sequence numbers to preallocate and store in memory for faster access. The minimum value is 1. Only one value can be generated at a time, i.e., NOCACHE. If unspecified, the old cache value is maintained.

CYCLE

The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue is reached by an ascending or descending sequence. If the limit is reached, the next number generated is the minvalue or maxvalue. If not specified, the old cycle behavior is maintained. You can use the keywords NO CYCLE to alter the sequence so that it doesn't recycle. However, this term isn't compatible with Oracle databases.

Notes

To avoid blocking concurrent transactions that obtain numbers from the same sequence, ALTER SEQUENCE is never rolled back. The changes take effect immediately and aren't reversible.

ALTER SEQUENCE doesn't immediately affect NEXTVAL results in backends, other than the current one, that have preallocated (cached) sequence values. They use up all cached values prior to noticing the changed sequence parameters. The current backend is affected immediately.

Examples

Change the increment and cache values of the sequence serial:

ALTER SEQUENCE serial INCREMENT BY 2 CACHE 5;