ALTER SEQUENCE

Change the definition of a sequence generator.

Summary

ALTER SEQUENCE [ IF EXISTS ] name [INCREMENT [ BY ] increment] 
     [MINVALUE minvalue | NO MINVALUE] 
     [MAXVALUE maxvalue | NO MAXVALUE] 
     [START [ WITH ] start ]
     [RESTART [ [ WITH ] restart] ]
     [CACHE cache] [[ NO ] CYCLE] 
     [OWNED BY {table.column | NONE}]

ALTER SEQUENCE [ IF EXISTS ] name OWNER TO new_owner

ALTER SEQUENCE [ IF EXISTS ] name RENAME TO new_name

ALTER SEQUENCE [ IF EXISTS ] name SET SCHEMA new_schema

describe

ALTER SEQUENCE Changes the parameters of an existing sequence generator. Any parameters that are not explicitly set in the ALTER SEQUENCE hit must maintain their previous settings.

The user must have this sequence to use ALTER SEQUENCE. To change the schema of a sequence, the user must also have CREATE privileges on the new schema. Note that superusers automatically have all privileges.

To change the owner, the user must be a direct or indirect member of the new role, and the new role must have CREATE permissions on the schema of the sequence (these restrictions force the change owner to not do anything that cannot be done by deleting and recreating the sequence. However, superusers can still change ownership of any sequence.)

Parameters

name

The name of the sequence to be modified (optional qualified).

IF EXISTS

If the sequence does not exist, no error will be thrown, but will only trigger one reminder.

Increment

clause INCREMENT BY increment is optional. A positive value will produce an ascending sequence, and a negative value will produce a descending sequence. If not specified, the old incremental value will be held.

minvalue

NO MINVALUE

Optional clause MINVALUE minvalue determines the minimum value a sequence can produce. If NO MINVALUE is specified, the default values for ascending and descending sequences are 1 and -263 - 1, respectively. If none of these options are specified, the current minimum value will be maintained.

maxvalue

NO MAXVALUE

Optional clause MAXVALUE maxvalue determines the maximum value a sequence can generate. If NO MAXVALUE is specified, the default values for ascending and descending sequences are 263 - 1 and -1, respectively. If none of these options are specified, the current maximum value will be maintained.

start

Optional clause START WITH start Changes the sequence start value of the record. This has no effect on the current sequence value. It simply sets the value that the future ALTER SEQUENCE RESTART command will use.

restart

Optional clause RESTART [ WITH restart ] Changes the current value of the sequence. This is equivalent to calling the setval(sequence, start_val, is_called) function using is_called = false. The specified value will be returned by the next call of the nextval(sequence) function. Writing RESTART without a restart value is equivalent to providing a start value that is set last by a CREATE SEQUENCE record or by an ALTER SEQUENCE START WITH.

new_owner

Username of the new owner of the sequence.

cache

The CACHE cache clause enables the sequence number to be pre-allocated and stored in memory to speed up access. The minimum value is 1 (only one value can be generated at a time, i.e. there is no cache). If not specified, the old cache value will be preserved

CYCLE

The optional CYCLE keyword can be used to wrap the sequence when it reaches maxvalue or minvalue from ascending or descending order. If the limit is reached, the next number generated will be the respective minvalue or maxvalue.

NO CYCLE

If an optional NO CYCLE keyword is specified, any call to nextval() will return an error after the sequence reaches its maximum. If CYCLE or NO CYCLE is not specified, the old loop behavior is preserved.

OWNED BY table.column

OWNED BY NONE

OWNED BY option associates a sequence with a specific table column so that if the column (or its entire table) is deleted, the sequence will also be automatically deleted. If specified, this association replaces any previously specified associations of the sequence. The specified table must have the same owner and the same schema as the sequence. Specifying OWNED BY NONE deletes any existing table column associations.

new_name

The new name of the sequence.

new_schema

New pattern of sequences.

Notice

To avoid blocking concurrent transactions that get numbers from the same sequence, the effect of ALTER SEQUENCE on sequence generation parameters is never rolled back. These changes will take effect immediately and are irreversible. However, the OWNED BY, OWNER TO, RENAME TO, and SET SCHEMA clauses are ordinary directory updates that can be rolled back.

ALTER SEQUENCE does not immediately affect nextval() results in sessions with preallocated (cache) sequence values except the current session. They will run out of all cached values before noticing the changed sequence generation parameters. The current session will be affected immediately.

ALTER TABLE can also be used with sequences for historical reasons. But the only variant of ALTER TABLE allowed by the sequence is equivalent to the above form.

Example

Restart a sequence called serial at 105:

ALTER SEQUENCE serial RESTART WITH 105;

compatibility

ALTER SEQUENCE complies with SQL standards, except for START WITH, OWNED BY, OWNER TO, RENAME TO, and SET SCHEMA clauses, which are extensions to the YMatrix database.