Sequence

A sequence is a database object that generates a series of unique numbers, most often used to produce primary key values automatically. Unlike an auto-increment column, a sequence exists independently of any table.

Because a sequence isn't tied to a specific table or column, the same sequence can be shared across several tables, or called directly to pre-generate an ID before an insert.

Syntax differs by database: Oracle uses sequence_name.NEXTVAL, PostgreSQL uses the nextval('sequence_name') function (or the SERIAL/IDENTITY shorthand that creates one automatically), and both support setting the starting value and increment.

Example

-- Oracle
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;
SELECT emp_seq.NEXTVAL FROM dual;

-- PostgreSQL
CREATE SEQUENCE emp_seq START 1 INCREMENT 1;
SELECT nextval('emp_seq');