9.4 Sequences

Sequences are generators stored as independent database objects. Sequences allow regular columns to grab values from them. By regular, I mean columns, that do not need any special modifiers. Sequences allow different columns from different tables to share the same generator. PostgreSQL, Oracle, and SQL Server support sequences, MySQL does not. They are created via the CREATE SEQUENCE command:

CREATE SEQUENCE sequence_name
  [ START WITH lower_bound INCREMENT BY increment ]

This partial syntax was taken from the PostgreSQL documentation. Please refer to your DBMS's manual for the specific syntax. The GENERATED clause and sequences have very similar syntaxes: both have a start value, an increment, a minimum value, a maximum value, and a cycle option. In standard SQL, you can use the sequence at hand for each insert with a statement like:

INSERT INTO Foo VALUES (NEXT VALUE FOR sequence_name, ...)

PostgreSQL has a special function to access sequences:

INSERT INTO Foo VALUES (nextval('sequence_name'), ...)

You can delete sequences by issuing a DROP SEQUENCE command. Note, that sequences can produce non-unique values.

Last updated: 2010-08-04