ALTER SEQUENCE

Function

ALTER SEQUENCE modifies 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. The system administrator has this permission by default. To modify a sequence owner, you must be the sequence owner or system administrator and a member of the new owner role.
• In the current version, you can modify only the owner, owning column, maximum value and increment. To modify other parameters, delete the sequence and create it again. Then, use the Setval function to restore parameter values.
• ALTER SEQUENCE MAXVALUE cannot be used in transactions, functions, and stored procedures.
• After the maximum value of a sequence is changed, the cache of the sequence in all sessions is cleared.
• The ALTER SEQUENCE statement blocks the invocation of nextval, setval, currval, and lastval.

Syntax

• Change the owning column of a sequence.

  AlterSequence ::= ALTER SEQUENCE [ IF EXISTS ] name
      [MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE | CACHE cache]
      [ OWNED BY { table_name'.'column_name | NONE } ]

• Change the owner of a sequence.

  AlterSequence ::= ALTER SEQUENCE [ IF EXISTS ] name OWNER TO new_owner;

• Change the increment of sequence.

  AlterSequence ::= ALTER SEQUENCE [ IF EXISTS ] name [ INCREMENT [ BY ] increment ];

Parameter Description

• 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

  For quick access, the number of serial numbers is pre-stored in the memory. If not specified, the old buffer value will be kept.

• OWNED BY

  Associates a sequence with a specified column included in a table. In this way, the sequence will be deleted when you delete its associated column or the table where the column belongs to.

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

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

  If OWNED BY NONE is used, all existing associations will be deleted.

• 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 CREATE permission on the sequence's schema.

Examples

-- Create an ascending sequence named serial, which starts from 101.
mogdb=# CREATE SEQUENCE serial START 101;

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

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

-- Change the increment of sequence.
mogdb=# ALTER SEQUENCE serial increment by 5;

-- Delete a sequence and a table.
mogdb=# DROP SEQUENCE serial cascade;
mogdb=# DROP TABLE T1;

Helpful Links

CREATE SEQUENCE，DROP SEQUENCE