ALTER SEQUENCE

Changes the attributes of an existing sequence. All changes take effect in the next database session. Any parameters not set by an ALTER SEQUENCE statement retain their previous settings.

Syntax

Parameters

schema

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject

If you do not specify a schema, the table is created in the default schema.

sequence‑name

The name of the sequence to alter. The name must be unique among sequences, tables, projections, and views.

RENAME TO new‑name

Renames a sequence within the same schema. To move a named sequence, use SET SCHEMA.

OWNER TO new‑owner‑name

Reassigns the current sequence owner to the specified owner.

Only the sequence owner or a superuser can change ownership, and reassignment does not transfer grants from the original owner to the new owner (grants made by the original owner are dropped).

SET SCHEMA new‑schema‑name

Moves a named sequence between schemas.

INCREMENT [BY] increment‑value

Modifies how much to increment or decrement the current sequence to create a new value. A positive value increments an ascending sequence, and a negative value decrements the sequence.

MINVALUE minvalue | NO MINVALUE

Modifies the minimum value a sequence can generate. If you change this value and the current value exceeds the range, the current value is changed to the minimum value if increment is greater than zero, or to the maximum value if increment is less than zero.

MAXVALUE maxvalue | NO MAXVALUE

Modifies the maximum value for the sequence. If you change this value and the current value exceeds the range, the current value is changed to the minimum value if increment is greater than zero, or to the maximum value if increment is less than zero.

RESTART [WITH] restart-value

Changes the current value of the sequence to restart‑value. The next call to NEXTVAL returns restart‑value.

Caution: Using ALTER SEQUENCE to set a sequence start value below its current value can result in duplicate keys.

CACHE value | NO CACHE 

Modifies how many sequence numbers are preallocated and stored in memory for faster access. The default is 250,000 with a minimum value of 1. Specifying a value of 1 indicates that only one value can be generated at a time, since no cache is assigned. Alternatively, you can specify NO CACHE.

CYCLE | NO CYCLE

Specifies whether the sequence can wrap when its minimum or maximum values are reached:

  • CYCLE: Allows the sequence to wrap around when the maxvalue or minvalue is reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated is the minvalue or maxvalue, respectively.
  • NO CYCLE (default): Calls to NEXTVAL return an error after the sequence reaches its maximum/minimum value.

Privileges

Examples

See Altering Sequences and Changing Sequence Ownership in the Administrator's Guide.

See Also

CREATE SEQUENCE