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:
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.
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 determines the maximum value limit the sequence is allowed to generate. The default maxvalue for ascending sequences is 2147483647 and -1 for descending sequences.
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.
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.
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.
Table 5-11. Create Table Defaults
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;
Example 5-32. Incrementing a Sequence
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:
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.
This returns the current value the sequence is at.
Example 5-33. Viewing the Current Value of a Sequence
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;
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)