ALTER SEQUENCE

Change the definition of a sequence generator.

Synopsis

ALTER SEQUENCE <name>
       [INCREMENT [BY] <value>]
       [MINVALUE <minvalue> | NO MINVALUE]
       [MAXVALUE <maxvalue> | NO MAXVALUE]
       [RESTART [ WITH ] <start>]
       [CACHE <cache>]
       [[NO] CYCLE]
       [OWNED BY { <table>.<column> | NONE }]

Description

ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameters not specifically set in the ALTER SEQUENCE command retain their prior setting.

You must own the sequence to use ALTER SEQUENCE.

Parameters

<name>

The name (optionally schema-qualified) of the sequence to be altered.

<increment>

Specifies which value is added to the current sequence value to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1.

<minvalue>
NO MINVALUE

Determines the minimum value a sequence can generate. If this clause is not supplied or NO MINVALUE is specified, then defaults will be used. The defaults are 1 and -263-1 for ascending and descending sequences, respectively.

<maxvalue>
NO MAXVALUE

Determines the maximum value for the sequence. If this clause is not supplied or NO MAXVALUE is specified, then default values will be used. The defaults are 263-1 and -1 for ascending and descending sequences, respectively.

<start>

The new current value of the sequence.

<cache>

Specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The minimum (and default) value is 1 (no cache).

CYCLE
NO CYCLE

Allows the sequence to wrap around when the <maxvalue> (for ascending) or <minvalue> (for descending) has been reached. If the limit is reached, the next number generated will be the <minvalue> (for ascending) or <maxvalue> (for descending). If NO CYCLE is specified, any calls to nextval() after the sequence has reached its maximum value will return an error. If not specified, NO CYCLE is the default.

OWNED BY <table>.<column>
OWNED BY NONE

Causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well. If specified, this association replaces any previously specified assocation for the sequence. The specified table must have the same owner and be in the same schema as the sequence. OWNED BY NONE removes any existing association.

Notes

ALTER SEQUENCE will not immediately affect nextval() results in backends, other than the current one, that have preallocated (cached) sequence values. They will use up all cached values prior to noticing the changed sequence generation parameters. The current backend will be affected immediately.

Some variants of ALTER TABLE can be used with sequences as well; for example, to rename a sequence use ALTER TABLE RENAME.

Examples

Restart the sequence named myseq:

ALTER SEQUENCE myseq RESTART WITH 111;

Change the increment value for the sequence named myseq:

ALTER SEQUENCE myseq INCREMENT BY 3;

Compatibility

CREATE SEQUENCE conforms to the SQL standard, with the exception that the OWNED BY clause is a HAWQ extension.

See Also

CREATE SEQUENCE, DROP SEQUENCE