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 ]
  

Parameters

seqname

Use this parameter to specify the name of the new sequence.

increment

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).

minvalue

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.

maxvalue

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.

start

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.

cache

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.

CYCLE

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.

Results

CREATE

This message is displayed if the sequence is created successfully.

ERROR: Relation 'seqname' already exists

This error if displayed if the sequence already exists. exists.

ERROR: DefineSequence: MINVALUE (start) can't be >= MAXVALUE (max)

This error is displayed if the sequence's minimum starting value is out of range.

ERROR: DefineSequence: START value (start) can't be < MINVALUE (min)

This error is displayed if the starting value is out of range.

ERROR: DefineSequence: MINVALUE (min) can't be >= MAXVALUE (max)

This error is displayed if the minimum and maximum values are inconsistent.

Description

Use the CREATE SEQUENCE command to register a new sequence number generator into the database.

Examples

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'
     );