Creating and Instantiating Named Sequences

You create a named sequence with CREATE SEQUENCE. The statement requires only a sequence name; all other parameters are optional.

The following example creates an ascending named sequence, my_seq, starting at the value 100:

=> create sequence my_seq START 100;
CREATE SEQUENCE

After creating a sequence, call the function NEXTVAL at least once in a session to create a cache for the sequence and its initial value. Subsequently, use NEXTVAL to increment the sequence value. Call the function CURRVAL to get the current value.

The following call to NEXTVAL instantiates the newly-created my_seq sequence and sets its first number:

=> SELECT NEXTVAL('my_seq');
 nextval
---------
     100
(1 row) 

If you call CURRVAL before NEXTVAL, the system returns an error:

dbt=> SELECT CURRVAL('my_seq');
ERROR 4700:  Sequence my_seq has not been accessed in the session

You can use a sequence as part of creating a table. The sequence must already exist, and have been instantiated using the NEXTVAL statement.

Update sequence number values by calling the NEXTVAL function, which increments/decrements the current sequence and returns the next value. Use CURRVAL to return the current value. These functions can also be used in INSERT and COPY expressions.

The following example shows creation of a simple table including a named sequence. Table creation is followed by an insert into the table using the sequence as the default value for the column ID:

CREATE TABLE customer2(  ID INTEGER DEFAULT NEXTVAL('my_seq'),
  lname VARCHAR(25), 
  fname VARCHAR(25),
  membership_card INTEGER
);
INSERT INTO customer2 VALUES (default,'Carr', 'Mary', 87432);

Now query the table you just created. The column named ID has been incremented by (1) again to 104:

SELECT * FROM customer2;
 ID  | lname | fname | membership_card
-----+-------+-------+-----------------
 104 | Carr  | Mary  |           87432
(1 row)

Using Named Sequence Functions

When you create a named sequence object, you can also specify its increment or decrement value. The default is 1.

Use these functions with named sequences:

Using DDL Statements with Named Sequences

The table that follows lists the statements you use specifically for named sequences.

Use this statement... To...
CREATE SEQUENCE Create a named sequence object.
ALTER SEQUENCE Alter named sequence parameters, rename a sequence within a schema, or move a named sequence between schemas.
DROP SEQUENCE

Remove a named sequence object.

GRANT SEQUENCE

Grant user privileges to a named sequence object. See also Sequence Privileges.