Creating and Using Sequences
A newer version of this documentation is available. Click here to view the most up-to-date release of the Greenplum 5.x documentation.
Creating and Using Sequences
You can use sequences to auto-increment unique ID columns of a table whenever a record is added. Sequences are often used to assign unique identification numbers to rows added to a table. You can declare an identifier column of type SERIAL to implicitly create a sequence for use with a column.
Creating a Sequence
The CREATE SEQUENCE command creates and initializes a 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
After you create 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 reset a sequence's counter value. For example:
SELECT setval('myserial', 201);
A nextval operation is never rolled back. Afetched value is considered used, even if the transaction that performed the nextval fails. This means that failed transactions can leave unused holes in the sequence of assigned values. setval operations are never rolled back.
Note that the nextval function is not allowed in UPDATE or DELETE statements if mirroring is enabled, and the currval and lastval functions are not supported in Greenplum Database.
To examine the current settings of a sequence, query the sequence table:
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 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;
Making a Sequence the Default Value for a Column
A sequence can be used in the CREATE TABLE command (instead of the SERIAL or BIGSERIAL type):
CREATE TABLE ... ( id INT4 DEFAULT nextval('myserial') );
Alternatively, a column can be altered and a sequence can be added as default value:
ALTER TABLE ... ALTER COLUMN id SET DEFAULT nextval('myserial');
By default, a sequence does not wrap around. That is, when the sequence reaches the max value (+32767 for SMALLSERIAL, +2147483647 for SERIAL, +9223372036854775807 for BIGSERIAL), every nextval() call produces an error. Alter the sequence to make it cycle around and start at 1 again:
ALTER SEQUENCE myserial CYCLE;
The wraparound behaviour can also specified when a sequence is created manually:
CREATE SEQUENCE myserial CYCLE;