CREATE SEQUENCE seqname [ INCREMENT increment ] [ MINVALUE minvalue ] [ MAXVALUE maxvalue ] [ START start ] [ CACHE cache ] [ CYCLE ]
Use this parameter to specify the name of the new sequence.
Use this parameter with the optional INCREMENT clause to either make an ascending sequence (with a positive number) or a descending sequence (with a negative number).
Use this parameter to specify the minimum value the new sequence can generate. The default is 1 for an ascending sequences and -2147483647 for for a descending sequence.
Use this parameter to specify the maximum value the new sequence can generate. The default is 1 for an ascending sequences and -2147483647 for for a descending sequence.
Use this parameter to set the starting location of the sequence. By default, a sequence will start at minvalue for ascending sequences and maxvalue for descending sequences.
Use this parameter to set the amount of sequence numbers that can be stored in cache memory; using cache in this manner will speed up performance. By default, this is set at 1, which forces generation of one number at a time (by default, cache is not used). Set it to a number higher than one to enable the use of caching.
Use this keyword to enable wrapping. When wrapping is enabled, a sequence can wrap around past its minimum or maximum value and begin again at its minimum or maximum value, depending on the type of sequence.
This message is displayed if the sequence is created successfully.
This error if displayed if the sequence already exists. exists.
This error is displayed if the sequence's minimum starting value is out of range.
This error is displayed if the starting value is out of range.
This error is displayed if the minimum and maximum values are inconsistent.
Use the CREATE SEQUENCE command to register a new sequence number generator into the database.
This example demonstrates the creation and use of a sequence named seq_invoice.
CREATE SEQUENCE seq_invoice START 200 CYCLE;
After creating the sequence, we select the next number from it.
SELECT NEXTVAL ('seq_invoice'); nextval ------- 200 (1 row)
The next example shows how to use the sequence in an INSERT command.
INSERT INTO shipped_orders VALUES ( NEXTVAL('seq_invoice'), '32.00', 200, '4/10/2001' );