3.22. Sequences

In the object hierarchy under every schema are Sequences. You can add sequences by selecting CREATE SEQUENCE from the Sequences context menu. Once a sequence has been created, it can be renamed, commented on, altered, and dropped, and its privileges can be edited. This section describes how to create, manage, and drop a sequence with Administrator.

PostgreSQL allows you to create sequences on a database in a schema. A sequence is a number generator that is restricted by various values such as start value, minimum value, maximum value, and increment. Each time the sequence is called upon, it returns the next acceptable number, given the restrictions in the definition of the sequence.

In this example, you will create a sequence named invoice_sequence.

1. A sequence is an object under a schema, so to create a sequence, first expand the database you wish to create the sequence under (click the [O-] beside the database name), then expand the public schema.

2. Right-click on Sequences and select CREATE SEQUENCE.

3. In the Name For The New Sequence field, type: invoice_sequence

4. Select the direction in which the sequence progresses. For this example, use Ascending as the direction.

5. Select the Increment. Each time the sequence is accessed, it generates a new number that differs from the previous number by this amount. For this example, set the Increment to 5.

6. Set the MinValue, the minimum value that the sequence can generate. For this example, set the MinValue to 0.

7. Set the MaxValue, the maximum value that the sequence can generate. For this example, set the MaxValue to 100000.

8. Set the Start value, the number generated by the sequence when it is invoked for the first time. For this example, set the Start value to 20.

9. Set the Cache, the number of values to be computed and stored in memory to prevent any slowdowns due to realtime computations. For this example, set Cache to 3.

   	Warning
   	Cache should be used very carefully; its use may generate unexpected results in certain cases. For example, when two backends are using the same sequence object, they each cache the values and change the sequence object. As a result, the sequence may not produce the expected results. Refer to the PostgreSQL 7.3 Reference Manual for further details.

10. Set whether the sequence should Cycle. If this box is checked, the sequence values restart once the MaxValue (for Ascending Sequences) or the MinValue (for Descending Sequences) has been reached. For this example, check the Cycle box.

    

    Figure 3-73. The CREATE SEQUENCE Dialog

11. Click OK to create the sequence.

Note that the sequence invoice_sequence has been added under the Sequences node in the Tree View.

Administrator also enables you to perform the following operations on an existing sequence: