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.

You can only modify a named sequence—that is, a sequence that was defined by CREATE SEQUENCE. AUTO_INCREMENT and IDENTITY sequences are owned by the table where they were created, and cannot be changed independently of that table.

Syntax

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 ]

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

Parameters

schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

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.

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.

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 in the Administrator's Guide.
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:

Schema privileges required… For these operations…
CREATE, USAGE Rename sequence

CREATE: destination schema
USAGE: current schema

 Move sequence to another schema

Examples

See Altering Sequences in the Administrator's Guide.

See Also

CREATE SEQUENCE