ALTER SEQUENCE

Description

Alters the parameters of an existing sequence.

Precautions

Only the sequence owner or a user granted with the ALTER permission can run the ALTER SEQUENCE command. System administrators have this permission by default. To modify a sequence owner, you must be the sequence owner or a system administrator and a member of the new owner role.
In the current version, you can modify only the owner, owning column, and maximum value. To change other parameters, drop the sequence and create it again. Then, use the Setval function to restore parameter values.
ALTER SEQUENCE MAXVALUE cannot be used in transactions, functions, or stored procedures.
After the maximum value of a sequence is changed, the cache of the sequence in all sessions is cleared.
If the LARGE identifier is used when a sequence is created, the LARGE identifier must be used when the sequence is altered.
The ALTER SEQUENCE statement blocks the calling of nextval, setval, currval, and lastval.

Syntax

Change the owning column of a sequence.

ALTER [ LARGE ] SEQUENCE [ IF EXISTS ] name 
    [MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE | CACHE cache]
    [ OWNED BY { table_name.column_name | NONE } ] ;

Change the owner of a sequence.

ALTER [ LARGE ] SEQUENCE [ IF EXISTS ] name OWNER TO new_owner;

Parameters

name
Specifies the name of the sequence to be modified.
IF EXISTS
Sends a notice instead of an error when you are modifying a nonexistent sequence.
CACHE
Specifies the number of sequences stored in the memory for quick access purposes. If this parameter is not specified, the old cache value is retained.
OWNED BY
Joins a sequence with a specified column included in a table. In this way, the sequence will be dropped when you drop its joined column or the table where the column belongs to.

If the sequence has been joined with another table before you use this option, the new join will overwrite the old one.

The joined table and sequence must be owned by the same user and in the same schema.

If OWNED BY NONE is used, all existing joins will be dropped.
new_owner
Specifies the username of the new owner of the sequence. To change the owner, you must also be a direct or indirect member of the new role, and this role must have the CREATE permission on the sequence's schema.

Examples

-- Create an ascending sequence named serial, starting from 101.
openGauss=# CREATE SEQUENCE serial START 101;

-- Create a table and specify default values for the sequence.
openGauss=# CREATE TABLE T1(C1 bigint default nextval('serial'));

-- Change the owning column of serial to T1.C1.
openGauss=# ALTER SEQUENCE serial OWNED BY T1.C1;

-- Drop a sequence and a table.
openGauss=# DROP SEQUENCE serial cascade;
openGauss=# DROP TABLE T1;