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 posi
tive value will make an ascending sequence, a nega
tive 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, respectively.
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 max
value for descending ones.
cache The CACHE cache option enables sequence numbers to
be preallocated 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 min
value or maxvalue, respectively.
OUTPUTS
CREATE Message returned if the command is successful.
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 single-row table with the name seqname.
The generator will be owned by the user issuing the com
mand.
After a sequence is created, you may use the function
nextval('seqname') to get a new number from the sequence.
The function currval('seqname') may be used to determine
the number returned by the last call to nextval('seqname')
for the specified sequence in the current 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 increment.
Use a query like
SELECT * FROM seqname;
to examine the parameters of a sequence. As an alterna
tive to fetching the parameters from the original defini
tion 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 transaction that did
the nextval later aborts. This means that aborted transac
tions may leave unused "holes" in the sequence of assigned
values. setval operations are never rolled back, either.
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 allo
cate distinct sequence values, the values may be
generated out of sequence when all the backends 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
generated 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 back
end, whether or not it has yet been returned by
nextval. Another consideration 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 num
bers. 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
-------
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.
Man(1) output converted with
man2html