Creating Sequences

   Sequences are not created automatically, like OIDs. Instead, you must use the CREATE SEQUENCE command. Three functions control the sequence counter, as shown in Table .

 

Table: Sequence number access functions

Function	Action
nextval('name')	Returns the next available sequence number, and updates the counter
currval('name')	Returns the sequence number from the previous nextval() call
setval('name', newval)	Sets the sequence number counter to the specified value

Figure  shows an example of sequence creation and sequence function usage.  

        test=> CREATE SEQUENCE functest_seq;
        CREATE
        test=> SELECT nextval('functest_seq');
         nextval 
        ---------
               1
        (1 row) 
         
        test=> SELECT nextval('functest_seq');
         nextval 
        ---------
               2
        (1 row) 
         
        test=> SELECT currval('functest_seq');
         currval 
        ---------
               2
        (1 row) 
         
        test=> SELECT setval('functest_seq', 100);
         setval 
        --------
            100
        (1 row) 
         
        test=> SELECT nextval('functest_seq');
         nextval 
        ---------
             101
        (1 row)  

The first command creates the sequence, then various sequence functions are called. Note that the SELECTs do not include a FROM clause. Sequence function calls are not directly tied to any table. In the figure:

• nextval() returns ever-increasing values.
• currval() returns the previous sequence value without incrementing.
• setval() sets the sequence counter to a new value.

Currval() returns the sequence number assigned by a prior nextval() call in the current session. It is not affected by the nextval() calls of other users, which allows reliable retrieval of nextval() assigned values in later queries.