Sequences in Greenplum

Sequences are often used to auto-increment unique ID columns of a table whenever a new record is added.

Creating a Sequence

The CREATE SEQUENCE command creates and initializes a new special single-row sequence generator table with the given sequence name. The sequence name must be distinct from the name of any other sequence, table, index, or view in the same schema. For example:

CREATE SEQUENCE myserial START 101;

Using a Sequence

Once you have created a sequence generator table using CREATE SEQUENCE, you can use the nextval function to operate on the sequence. For example, to insert a row into a table that gets the next value of a sequence:

INSERT INTO vendors VALUES (nextval('myserial'), 'acme');

You can also use the setval function to operate on a sequence to reset a sequence’s counter value. For example:

SELECT setval('myserial', 201);

A nextval operation is never rolled back. Once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions may leave unused holes in the sequence of assigned values. setval operations are never rolled back, either.

Note that the nextval function is currently not allowed in UPDATE or DELETE statements if mirroring is enabled, and the currval and lastval functions are currently not supported in Greenplum Database.

To then examine the current settings of a sequence you can simply query the sequence table directly:

SELECT * FROM myserial;

Altering a Sequence

The ALTER SEQUENCE command changes the parameters of an existing sequence generator. For example:

ALTER SEQUENCE myserial RESTART WITH 105;

Any parameters not specifically set in the ALTER SEQUENCE command retain their prior settings.

Dropping a Sequence

The DROP SEQUENCE command removes a sequence generator table. For example:

DROP SEQUENCE myserial;