ALTER SEQUENCE

Changes a sequence in two ways:

• Resets parameters that control sequence behavior—for example, its start value, and range of minimum and maximum values. These changes take effect only when you start a new database session.
• Resets sequence name, schema, or ownership. These changes take effect immediately.

Syntax

Change sequence behavior:

ALTER SEQUENCE [[database.]schema.]sequence
    [ INCREMENT [ BY ] integer ]
    [ MINVALUE integer | NO MINVALUE ]
    [ MAXVALUE integer | NO MAXVALUE ]
    [ RESTART [ WITH ] integer ]
    [ CACHE integer | NO CACHE ]
    [ CYCLE | NO CYCLE ]

Change sequence name, schema, or ownership:

ALTER SEQUENCE [schema.]sequence-name {
    RENAME TO seq-name
    | SET SCHEMA schema-name]
    | OWNER TO owner-name
}

Parameters

schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

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

sequence

Name of the sequence to alter.

In the case of IDENTITY table columns, Vertica generates the sequence name using the following convention:

table-name_col-name_seq

To obtain this name, query the SEQUENCES system table.

INCREMENT

Positive or negative integer that specifies how much to increment or decrement the sequence on each call to NEXTVAL, by default set to 1.

Note

Setting this parameter to integer guarantees that column values always increment by at least integer. However, column values can sometimes increment by more than integer unless you also set the NO CACHE parameter.

MINVALUE|NO MINVALUE

Maximum integer value of the sequence. Vertica automatically changes the sequence value in two cases:

• Ascending sequence: If currentSequenceValue<newMinValue, sequence value resets to newMinValue.
• Descending sequence: If currentSequenceValue<newMinValue, sequence value cycles back to MAXVALUE.

MAXVALUE|NO MAXVALUE

Maximum integer value of the sequence. Vertica automatically changes the sequence value in two cases:

• Ascending sequence: If currentSequenceValue>newMaxValue, sequence value cycles back to MINVALUE.
• Descending sequence: If currentSequenceValue > newMaxValue, sequence value resets to to newMaxValue.

RESTART

New integer start value of the sequence. The next call to NEXTVAL returns the new start value.

Caution

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

CACHE|NO CACHE

Whether to cache unique sequence numbers on each node for faster access. CACHE takes an integer argument as follows:

• >1 specifies how many unique sequence numbers are pre-allocated and stored in memory for faster access. Vertica sets up caching for each session, and distributes it across all nodes.

  Caution

  If sequence caching is set to a low number, nodes are liable to request a new set of cache values more frequently. While it supplies a new cache, Vertica must lock the catalog. Until Vertica releases the lock, other database activities such as table inserts are blocked, which can adversely affect overall performance.

• 0 or 1 specifies to disable caching (equivalent to NO CACHE).

By default, the sequence cache is set to 250,000.

For details, see Distributing sequences.

CYCLE|NO CYCLE

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

• CYCLE: The sequence wraps as follows:

  • When an incrementing sequence reaches its upper limit, it is reset to its minimum value.

  • When an decrementing sequence reaches its lower limit, it is reset to its maximum value.

• NO CYCLE (default): Calls to NEXTVAL return an error after the sequence reaches its maximum or minimum value.

RENAME TO

Supported only for named sequences, renames a sequence within the current schema, where seq-name conforms to conventions described in Identifiers. It must also be unique among all names of sequences, tables, projections, views, and models within the same schema.

SET SCHEMA

Supported only for named sequences, moves the sequence to schema schema-name.

OWNER TO

Reassigns sequence ownership to another user.

Privileges

For named sequences, USAGE on the schema and one of the following:

• Sequence owner

• ALTER privilege on the sequence
  For certain operations, non-superusers must have the following schema privileges:

  Schema privileges required...	For these operations...
  CREATE, USAGE	Rename sequence
  CREATE: destination schema USAGE: current schema	Move sequence to another schema

For IDENTITY column sequences, USAGE on the table schema and one of the following:

• Table owner

• ALTER privileges

Non-superusers must also have SELECT privileges to enable or disable constraint enforcement, or remove partitioning.

Examples

See Altering sequences.

See also