CREATE SEQUENCE -- Creates a new sequence number generator
CREATE SEQUENCE seqname
[ INCREMENT increment ]
[ MINVALUE minvalue ]
[ MAXVALUE maxvalue ]
[ START start ]
[ CACHE cache ]
[ CYCLE ]
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 command.
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 get 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.
Low-level locking is used to enable multiple simultaneous calls to a generator.
Use DROP SEQUENCE to remove a sequence.
Each backend uses its own cache to store allocated numbers. Numbers that are cached but not used in the current session will be lost, resulting in "holes" in the sequence.
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;
CREATE SEQUENCE is a Postgres language extension. There is no CREATE SEQUENCE statement in SQL92.