ALTER SEQUENCE

Changes a sequence in two ways:

• Changes values 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.
• Changes 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 name
    | SET SCHEMA schema]
    | OWNER TO owner
}

Arguments

[database.]schema

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

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 integer

Positive or negative minimum value change on each call to NEXTVAL. The default is 1.

This value is a minimum increment size. The sequence can increment by more than this value unless you also specify NO CACHE.

MINVALUE integer | NO MINVALUE

Minimum value the sequence can generate. If this change would invalidate the current sequence position, the operation fails.

MAXVALUE integer | NO MAXVALUE

Maximum value the sequence can generate. If this change would invalidate the current sequence position, the operation fails.

RESTART integer

New 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

How many unique sequence numbers to pre-allocate and store in memory on each node for faster access. Vertica sets up caching for each session and distributes it across all nodes. A value of 0 or 1 is equivalent to NO CACHE.

Caution

If sequence caching is set to a low number, nodes might 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.

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

For details, see Distributing sequences.

CYCLE | NO CYCLE

How to handle reaching the end of the sequence. The default is NO CYCLE, meaning that a call to NEXTVAL returns an error after the sequence reaches its upper or lower limit. CYCLE instead wraps, as follows:

• Ascending sequence: wraps to the minimum value.
• Descending sequence: wraps to the maximum value.

RENAME TO name

New name in the current schema for the sequence. Supported only for named sequences.

SET SCHEMA schema

Moves the sequence to a new schema. Supported only for named sequences.

OWNER TO owner

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:

  • To rename a sequence: CREATE, USAGE
  • To move a sequence to another schema: CREATE on the destination, USAGE on the source

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