Creating and Using Sequences

A Greenplum Database sequence object is a special single row table that functions as a number generator. You can use a sequence to generate unique integer identifiers for a row that you add to a table. Declaring a column of type SERIAL implicitly creates a sequence counter for use in that table column.

Greenplum Database provides commands to create, alter, and drop a sequence. Greenplum Database also provides built-in functions to return the next value in the sequence (nextval()) or to set the sequence to a specific start value (setval()).

Note

The PostgreSQL currval() and lastval() sequence functions are not supported in Greenplum Database.

Attributes of a sequence object include the name of the sequence, its increment value, and the last, minimum, and maximum values of the sequence counter. Sequences also have a special boolean attribute named is_called that governs the auto-increment behavior of a nextval() operation on the sequence counter. When a sequence's is_called attribute is true, nextval() increments the sequence counter before returning the value. When the is_called attribute value of a sequence is false, nextval() does not increment the counter before returning the value.

Parent topic: Defining Database Objects

Creating a Sequence

The CREATE SEQUENCE command creates and initializes a sequence with the given sequence name and optional start value. 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;

When you create a new sequence, Greenplum Database sets the sequence is_called attribute to false. Invoking nextval() on a newly-created sequence does not increment the sequence counter, but returns the sequence start value and sets is_called to true.

Using a Sequence

After you create a sequence with the CREATE SEQUENCE command, you can examine the sequence and use the sequence built-in functions.

Examining Sequence Attributes

To examine the current attributes of a sequence, query the sequence directly. For example, to examine a sequence named myserial:

SELECT * FROM myserial;

Returning the Next Sequence Counter Value

You can invoke the nextval() built-in function to return and use the next value in a sequence. The following command inserts the next value of the sequence named myserial into the first column of a table named vendors:

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

nextval() uses the sequence's is_called attribute value to determine whether or not to increment the sequence counter before returning the value. nextval() advances the counter when is_called is true. nextval() sets the sequence is_called attribute to true before returning.

A nextval() operation is never rolled back. A fetched 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.

Note

You cannot use the nextval() function in UPDATE or DELETE statements if mirroring is enabled in Greenplum Database.

Setting the Sequence Counter Value

You can use the Greenplum Database setval() built-in function to set the counter value for a sequence. For example, the following command sets the counter value of the sequence named myserial to 201:

SELECT setval('myserial', 201);

setval() has two function signatures: setval(sequence, start_val) and setval(sequence, start_val, is_called). The default behaviour of setval(sequence, start_val) sets the sequence is_called attribute value to true.

If you do not want the sequence counter advanced on the next nextval() call, use the setval(sequence, start_val, is_called) function signature, passing a false argument:

SELECT setval('myserial', 201, false);

setval() operations are never rolled back.

nextval() and setval() Caution

To avoid blocking concurrent transactions that obtain numbers from the same sequence, the value obtained by nextval() is not reclaimed for re-use if the calling transaction later aborts. As such, transaction aborts or database crashes can result in gaps in the sequence of assigned values. This situation can occur without a transaction abort, also. For example, an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including invoking any required nextval() calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. Consequently, you cannot use Greenplum Database sequence objects to obtain "gapless" sequences.

Similarly, sequence state changes made by setval() are immediately visible to other transactions, and are not undone if the calling transaction rolls back.

If the database cluster crashes before committing a transaction containing a nextval() or setval() call, the sequence state change may not have been propogated to persistent storage, so that it is uncertain whether the sequence has its original or updated state after the cluster restarts. This is harmless for usage of the sequence within the database, since other effects of uncommitted transactions are visible either. However, if you wish to use a sequence value for persistent outside-the-database purposes, ensure that the nextval() call has been committed before doing so.

Altering a Sequence

The ALTER SEQUENCE command changes the attributes of an existing sequence. You can alter the sequence start, minimum, maximum, and increment values. You can also restart the sequence at the start value or at a specified value.

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

ALTER SEQUENCE sequence START WITH start_value sets the sequence's start_value attribute to the new starting value. It has no effect on the last_value attribute or the value returned by the nextval(sequence) function.

ALTER SEQUENCE sequence RESTART resets the sequence's last_value attribute to the current value of the start_value attribute and the is_called attribute to false. The next call to the nextval(sequence) function returns start_value.

ALTER SEQUENCE sequence RESTART WITH restart_value sets the sequence's last_value attribute to the new value and the is_called attribute to false. The next call to the nextval(sequence) returns restart_value. This is the equivalent of calling setval(sequence, restart_value, false).

The following command restarts the sequence named myserial at value 105:

ALTER SEQUENCE myserial RESTART WITH 105;

Dropping a Sequence

The DROP SEQUENCE command removes a sequence. For example, the following command removes the sequence named myserial:

DROP SEQUENCE myserial;

Specifying a Sequence as the Default Value for a Column

You can reference a sequence directly in the CREATE TABLE command in addition to using the SERIAL or BIGSERIAL types. For example:

CREATE TABLE tablename ( id INT4 DEFAULT nextval('myserial'), name text );

You can also alter a table column to set its default value to a sequence counter:

ALTER TABLE tablename ALTER COLUMN id SET DEFAULT nextval('myserial');

Sequence Wraparound

By default, a sequence does not wrap around. That is, when a sequence reaches the max value (+32767 for SMALLSERIAL, [phone] for SERIAL, +9223372036854775807 for BIGSERIAL), every subsequent nextval() call produces an error. You can alter a sequence to make it cycle around and start at 1 again:

ALTER SEQUENCE myserial CYCLE;

You can also specify the wraparound behaviour when you create the sequence:

CREATE SEQUENCE myserial CYCLE;