Sequences

A sequence in PostgreSQL is a special database object that generates a series of unique integer values. Sequences are the underlying mechanism that powers SERIAL columns and GENERATED AS IDENTITY columns. You can also create and use sequences independently to generate unique numbers that span multiple tables or follow custom patterns such as starting from 1000 or incrementing by 10.

CREATE SEQUENCE syntax

CREATE SEQUENCE [IF NOT EXISTS] sequence_name
  [AS { SMALLINT | INT | BIGINT }]
  [INCREMENT [BY] increment]
  [MINVALUE minvalue | NO MINVALUE]
  [MAXVALUE maxvalue | NO MAXVALUE]
  [START [WITH] start]
  [CACHE cache]
  [[ NO ] CYCLE]
  [OWNED BY { table_name.column_name | NONE }];

Key options:

• INCREMENT — how much to add each call; positive for ascending, negative for descending. Default is 1.
• START — the first value returned by nextval(). Default is 1 for ascending sequences.
• MINVALUE / MAXVALUE — the lower and upper bounds.
• CYCLE — restart from MINVALUE (ascending) or MAXVALUE (descending) when the limit is reached. Default is NO CYCLE, which raises an error at the limit.
• CACHE — how many values to preallocate in memory per session for faster access.
• OWNED BY — ties the sequence to a table column; the sequence is dropped automatically when the column or table is dropped.

Sequence examples

Example 1 — ascending sequence starting at 100, incrementing by 5:

CREATE SEQUENCE mysequence INCREMENT 5 START 100;

SELECT nextval('mysequence');
-- Returns: 100

SELECT nextval('mysequence');
-- Returns: 105

Example 2 — cycling descending sequence from 3 to 1:

CREATE SEQUENCE three
  INCREMENT -1
  MINVALUE 1
  MAXVALUE 3
  START 3
  CYCLE;

SELECT nextval('three'); -- 3
SELECT nextval('three'); -- 2
SELECT nextval('three'); -- 1
SELECT nextval('three'); -- 3 (cycles back)

Example 3 — sequence owned by a table column:

CREATE TABLE order_details (
  order_id  SERIAL,
  item_id   INT NOT NULL,
  item_text VARCHAR NOT NULL,
  price     DEC(10, 2) NOT NULL,
  PRIMARY KEY (order_id, item_id)
);

CREATE SEQUENCE order_item_id
  START 10 INCREMENT 10 MINVALUE 10
  OWNED BY order_details.item_id;

INSERT INTO order_details (order_id, item_id, item_text, price)
VALUES
  (100, nextval('order_item_id'), 'DVD Player', 100),
  (100, nextval('order_item_id'), 'Android TV', 550),
  (100, nextval('order_item_id'), 'Speaker',    250);

List all sequences in the current database:

SELECT relname AS sequence_name
FROM pg_class
WHERE relkind = 'S';

Drop a sequence:

DROP SEQUENCE [IF EXISTS] sequence_name [CASCADE | RESTRICT];

Sequence tips

• Sequence operations are not transaction-safe: a rolled-back transaction still consumes the value, leaving a gap. Never rely on gap-free sequential IDs in business logic.
• Use CACHE greater than 1 to improve insert throughput in high-concurrency environments, at the cost of larger potential gaps.
• Use OWNED BY to bind the sequence to its column so it is cleaned up automatically when the table is dropped or the column is removed.
• For standard auto-increment IDs, prefer SERIAL or GENERATED AS IDENTITY over manually creating sequences — they handle ownership and defaults automatically.

Reference: PostgreSQL documentation — CREATE SEQUENCE.