ALTER SEQUENCE

Description

Modifies the parameters of an existing sequence.

Precautions

• Only the owner of a sequence, a user granted the ALTER permission on a sequence, or a user granted the ALTER ANY SEQUENCE permission on a sequence can run the ALTER SEQUENCE command. When separation of duties is disabled, a 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, and maximum value. 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 calling of nextval, setval, currval, and lastval.

Syntax

• Change the maximum sequence value and owning column.

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

  

• Change the owner of a sequence.

  ALTER SEQUENCE [ IF EXISTS ] name OWNER TO new_owner;

  

Parameters

• name

  Specifies the name of the sequence to be modified.

• IF EXISTS

  This option is used when the sequence does not exist. ERROR is not displayed. Instead, a NOTICE message is returned.

• MAXVALUE maxvalue | NO MAXVALUE | NOMAXVALUE

  Specifies the maximum value of the sequence. The new maximum value must be greater than last_value. If no new value is specified, the old maximum value is retained.

  Value range: (last_value, 2⁶³ – 1]. If LARGE is used, the range is (last_value, 2¹²⁷ – 1].

• 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 the CREATE permission on the sequence's schema. For details about the username requirements, see •user_name.

Examples

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

-- Query the next value of serial.
m_db=# SELECT pg_catalog.nextval('serial');
 nextval 
---------
     101
(1 row)

-- Change the maximum value of serial to 101.
m_db=# ALTER SEQUENCE serial MAXVALUE 101;
ERROR:  Can not alter sequence uuid 1000011 maxval from 9223372036854775807 to 101, because new maxval 101 should be greater than currval 101 in gtm.

-- Delete the sequence.
m_db=# DROP SEQUENCE serial CASCADE;

Helpful Links

CREATE SEQUENCE and DROP SEQUENCE