CREATE SEQUENCE() SQL Commands CREATE SEQUENCE()NAME
CREATE SEQUENCE - Creates a new sequence number generator
SYNOPSIS
CREATE SEQUENCE seqname [ INCREMENT increment ]
[ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
[ START start ] [ CACHE cache ] [ CYCLE ]
INPUTS
seqname
The name of a sequence to be created.
increment
The INCREMENT increment clause is optional. A positive value
will make an ascending sequence, a negative one a descending
sequence. The default value is one (1).
minvalue
The optional clause MINVALUE minvalue determines the minimum
value a sequence can generate. The defaults are 1 and
-2147483647 for ascending and descending sequences, respec‐
tively.
maxvalue
The optional clause MAXVALUE maxvalue determines the maximum
value for the sequence. The defaults are 2147483647 and -1 for
ascending and descending sequences, respectively.
start The optional START start clause enables the sequence to begin
anywhere. The default starting value is minvalue for ascending
sequences and maxvalue for descending ones.
cache The CACHE cache option enables sequence numbers to be preallo‐
cated and stored in memory for faster access. The minimum value
is 1 (only one value can be generated at a time, i.e., no cache)
and this is also the default.
CYCLE The optional CYCLE keyword may be used to enable the sequence to
wrap around when the maxvalue or minvalue has been reached by an
ascending or descending sequence respectively. If the limit is
reached, the next number generated will be the minvalue or max‐
value, respectively.
OUTPUTS
CREATE Message returned if the command is successful.
ERROR: Relation 'seqname' already exists
If the sequence specified already exists.
ERROR: DefineSequence: MINVALUE (start) can't be >= MAXVALUE (max)
If the specified starting value is out of range.
ERROR: DefineSequence: START value (start) can't be < MINVALUE (min)
If the specified starting value is out of range.
ERROR: DefineSequence: MINVALUE (min) can't be >= MAXVALUE (max)
If the minimum and maximum values are inconsistent.
DESCRIPTION
CREATE SEQUENCE will enter a new sequence number generator into the
current data base. This involves creating and initializing a new sin‐
gle-row table with the name seqname. The generator will be owned by
the user issuing the command.
After a sequence is created, you may use the function nextval('seq‐
name') to get a new number from the sequence. The function cur‐
rval('seqname') may be used to determine the number returned by the
last call to nextval('seqname') for the specified sequence in the cur‐
rent session. The function setval('seqname', newvalue) may be used to
set the current value of the specified sequence. The next call to
nextval('seqname') will return the given value plus the sequence incre‐
ment.
Use a query like
SELECT * FROM seqname;
to examine the parameters of a sequence. As an alternative to fetching
the parameters from the original definition as above, you can use
SELECT last_value FROM seqname;
to obtain the last value allocated by any backend.
To avoid blocking of concurrent transactions that obtain numbers from
the same sequence, a nextval operation is never rolled back; that is,
once a value has been fetched it is considered used, even if the trans‐
action that did the nextval later aborts. This means that aborted
transactions may leave unused "holes" in the sequence of assigned val‐
ues. setval operations are never rolled back, either.
Caution: Unexpected results may be obtained if a cache setting
greater than one is used for a sequence object that will be used
concurrently by multiple backends. Each backend will allocate
and cache successive sequence values during one access to the
sequence object and increase the sequence object's last_value
accordingly. Then, the next cache-1 uses of nextval within that
backend simply return the preallocated values without touching
the shared object. So, numbers allocated but not used in the
current session will be lost. Furthermore, although multiple
backends are guaranteed to allocate distinct sequence values,
the values may be generated out of sequence when all the back‐
ends are considered. (For example, with a cache setting of 10,
backend A might reserve values 1..10 and return nextval=1, then
backend B might reserve values 11..20 and return nextval=11
before backend A has generated nextval=2.) Thus, with a cache
setting of one it is safe to assume that nextval values are gen‐
erated sequentially; with a cache setting greater than one you
should only assume that the nextval values are all distinct, not
that they are generated purely sequentially. Also, last_value
will reflect the latest value reserved by any backend, whether
or not it has yet been returned by nextval. Another considera‐
tion is that a setval executed on such a sequence will not be
noticed by other backends until they have used up any preallo‐
cated values they have cached.
NOTES
Use DROP SEQUENCE to remove a sequence.
Each backend uses its own cache to store preallocated numbers. Numbers
that are cached but not used in the current session will be lost,
resulting in "holes" in the sequence.
USAGE
Create an ascending sequence called serial, starting at 101:
CREATE SEQUENCE serial START 101;
Select the next number from this sequence:
SELECT NEXTVAL ('serial');
nextval
-------
114
Use this sequence in an INSERT:
INSERT INTO distributors VALUES (NEXTVAL('serial'),'nothing');
Set the sequence value after a COPY FROM:
CREATE FUNCTION distributors_id_max() RETURNS INT4
AS 'SELECT max(id) FROM distributors'
LANGUAGE 'sql';
BEGIN;
COPY distributors FROM 'input_file';
SELECT setval('serial', distributors_id_max());
END;
COMPATIBILITY
SQL92
CREATE SEQUENCE is a Postgres language extension. There is no CREATE
SEQUENCE statement in SQL92.
SQL - Language Statements 29 March 2001 CREATE SEQUENCE()
