A sequence is a database object which generates a consecutive order of numbers. Sequences are listed under the list of database relations (this list appears when a \d is performed in psql). Sequences are also commonly referred to as auto-increment.

The CREATE SEQUENCE command creates a sequence number generator. The sequence can be specified to increment or decrement. The syntax to create a sequence is:

    CREATE SEQUENCE 

seqname


          INCREMENT 

increment_val


           MINVALUE 

minvalue


           MAXVALUE 

maxvalue


              START 

start_val


              CACHE 

cache


              CYCLE

A sequence uses the integer data type. Therefore, its minimum, maximum, and start values all have the restriction of falling between -2147483647 and +2147483647. The only keyword required to produce a sequence is CREATE SEQUENCE . All other clauses are optional. These optional clauses are defined below:

INCREMENT increment_val

This ascends when a positive value is used for the increment_val . This descends when a negative value is used for the increment_val . The default value is 1.

MINVALUE minvalue

This clause determines the minimum value limit the sequence is allowed to generate. The default minvalue is 1 for ascending sequences and -2147483647 for descending sequences.

MAXVALUE maxvalue

MAXVALUE determines the maximum value limit the sequence is allowed to generate. The default maxvalue for ascending sequences is 2147483647 and -1 for descending sequences.

START start_val

This clause allows the user to define a sequence to start anywhere in the allowed integer scale. The sequence defaults to start at minvalue for ascending sequences and maxvalue for descending sequences.

CACHE cache

This option provides the ability for the sequence numbers to be pre-allocated and stored in memory. This functionality aids in faster access. The minimum and default value is 1.

CYCLE

The CYCLE clause enables the sequence to continue generating a sequence even though it has reached its max or min value. When the limit is reached, it starts over at the min value for ascending sequences and max value for descending sequences.

The table below summarizes the defaults for clauses in the CREATE TABLE command for ascending and descending sequences.

Table 5-11. Create Table Defaults

Clause Ascending Descending
MINVALUE 1 -2147483647
MAXVALUE 2147483647 -1
START minvalue maxvalue
CYCLE minvalue maxvalue

This creates a simple ascending sequence that starts at 1 and cycles back to 1 when it reaches the maximum limit:

Example 5-30. Creating a Sequence

     CREATE SEQUENCE seq_invoice
            INCREMENT 1
            CYCLE;
   

Even though you can perform a backslash d (\d) to see if an object is a sequence or not, it is advisable to name it something that will let a user know that it is a sequence. Another way to view a sequence is to look at its parameters. Use this select command to display information about the sequence:

Example 5-31. Viewing a Sequence

   SELECT * FROM seq_invoice;
   

Similar to a table, you can query columns from the sequence and view only specific values of a sequence. The command below only displays the last value and maximum value of a sequence:

   SELECT last_value, max_value
     FROM seq_invoice;
   

To increment a sequence, use the the nextval function:

Example 5-32. Incrementing a Sequence

   SELECT nextval('seq_invoice');
   

Each time you use the above command, it increments the sequence to the next possible value. There are other functions which can be used to manipulate a sequence. These functions are:

nextval (' seqname ')

If the seqname is a newly created sequence, then this will retrieve a new number from the sequence. If seqname is an existing sequence, then it will return the current value plus the increment.

currval (' seqname '

This returns the current value the sequence is at.

Use this to look at the current value of the invoice sequence:

Example 5-33. Viewing the Current Value of a Sequence

        SELECT currval('seq_invoice');
      

setval(' seqname ', newvalue )

This sets the current value of the specified sequence.

Sequences can be very useful in SQL commands. For instance, we will create a table and default the table's values to a sequence. The sequence will increment whenever a user inserts new data and does not specify a value for that column. The sequence must exist for this table to use it as a default.

First, create a sequence that begins at employee number 100.

   CREATE SEQUENCE seq_emp_id START 100;
   

Then we can assign this sequence as the default for the employee identifier number when we create the employee table. Create the table using this command:

Example 5-34. Setting a Column to Default to a Sequence

   CREATE TABLE employee 
          (
          emp_id integer default nextval('seq_emp_id'),
          name text
          );
   

When you perform an insertion into the table without specifying a value for the employee identification number, it will default to the next value of the sequence. Try inserting a few values like we have below and view the resulting table.

   INSERT INTO employee (name) values ('Jonathan M.');
   INSERT INTO employee (name) values ('Jessica T.');
   INSERT INTO employee (name) values ('Jennifer L.');
   INSERT INTO employee (name) values ('Mark L.');
   

If you view this table, it now contains some default employee identification numbers generated by the sequence and the names you have just passed into it.

 emp_id |    name
--------+------------
    100 | Jonathan M.
    101 | Jessica T.
    102 | Jennifer L.
    103 | Mark L.
(4 rows)