Sequence manipulation functions v16

Sequence objects, which are also called sequence generators or sequences, are special single-row tables created with the CREATE SEQUENCE command. You usually use a sequence to generate unique identifiers for rows of a table. The sequence functions provide simple, multiuser-safe methods for obtaining successive sequence values from sequence objects.

sequence.NEXTVAL
sequence.CURRVAL

sequence is the identifier assigned to the sequence in the CREATE SEQUENCE command.

NEXTVAL

Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute NEXTVAL concurrently, each safely receives a distinct sequence value.

CURRVAL

Return the value most recently obtained by NEXTVAL for this sequence in the current session. An error is reported if NEXTVAL was never called for this sequence in this session. Because this is returning a session-local value, it gives a predictable answer whether or not other sessions executed NEXTVAL since the current session did.

If a sequence object was created with default parameters, NEXTVAL calls on it return successive values beginning with 1. Use special parameters in the CREATE SEQUENCE command for other behaviors.

Important

To avoid blocking of concurrent transactions that obtain numbers from the same sequence, a NEXTVAL operation is never rolled back. After a value is fetched it is considered used, even if the transaction that did the NEXTVAL later aborts. This means that aborted transactions can leave unused "holes" in the sequence of assigned values.