Sequences

Creating a Sequence

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:

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

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

     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:

   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:

   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:

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:

   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)  
   

Destroying a Sequence

To destroy a sequence generator, use the drop sequence command. You can list several sequences to be dropped at a time. The syntax for drop sequence is:

   DROP SEQUENCE seq_name1, seq_name2, ...;
   

This removes the invoice sequence from the database:

   DROP SEQUENCE seq_invoice;
   

	Caution
	Before destroying a sequence, make sure that the sequence is not used by another table, function, or any other object in the database. If this check is not performed, then other operations that use the sequence will fail.