ALTER SEQUENCE

Changes a named sequence in two ways:

• Sets 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.

• Sets 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

The name of the sequence to alter.

INCREMENT [BY] integer

A 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 integer
NO MINVALUE (default)

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 integer
NO MAXVALUE (default)

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] integer

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

Caution

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

CACHE integer
NO CACHE (default)

Specifies how many 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. By default, the sequence cache is set to 250,000.

For details, see Distributing named sequences.

CYCLE
NO CYCLE (default)

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 seq-name

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 schema-name

Moves the sequence to schema schema-name.

OWNER TO owner-name

Reassigns the current sequence owner to the specified owner.

Privileges

Non-superuser: 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:

Examples

See Altering sequences.

See also