Sequences

Definition

A sequence is a standalone, schema-level object that generates a series of unique numeric values, independent of any single table or column. Unlike an identity column, a sequence is created, altered, and dropped on its own, and its values can be pulled by any number of tables or even application code.

sql
CREATE SEQUENCE order_seq
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1
    CACHE 20;

-- Using it explicitly
INSERT INTO orders (id, customer_id) VALUES (nextval('order_seq'), 42);

-- Or as a column default
CREATE TABLE invoices (
    id     INT DEFAULT nextval('order_seq') PRIMARY KEY,
    amount NUMERIC(10,2)
);

Key functions: nextval('seq') advances and returns the next value; currval('seq') returns the last value obtained in the current session; setval('seq', n) manually resets the counter.

Worked Example — Sharing One Sequence Across Tables

A business wants globally unique document numbers across two different document types stored in separate tables:

sql
CREATE SEQUENCE document_number_seq START WITH 100000;

CREATE TABLE invoices (
    id  INT DEFAULT nextval('document_number_seq') PRIMARY KEY,
    ...
);

CREATE TABLE credit_notes (
    id  INT DEFAULT nextval('document_number_seq') PRIMARY KEY,
    ...
);

Now an invoice numbered 100001 and a credit note numbered 100002 can never collide, because both draw from the same counter — something a per-table identity column cannot do on its own.

Edge Cases and Pitfalls

• CACHE causes gaps and non-strict ordering: CACHE 20 pre-allocates 20 values to a session for speed; if the server restarts or the session disconnects, unused cached values are lost forever, creating visible jumps. Concurrent sessions caching blocks also means values are not guaranteed to be handed out in strict chronological/insert order across sessions.
• Not rolled back on transaction abort: calling nextval() inside a transaction that later rolls back does not give the value back — sequence advancement is intentionally non-transactional in PostgreSQL, to avoid serializing all inserts through one row.
• CYCLE risk: a sequence defined CYCLE wraps back to MINVALUE after hitting MAXVALUE. If old rows using low values from a previous cycle still exist, this can produce duplicate values (which then may violate a UNIQUE/primary key constraint, or worse, silently overwrite if there's no uniqueness constraint at all).
• `ALTER SEQUENCE ... RESTART WITH n` is the correct way to manually reset a sequence, e.g. after a bulk data migration that inserted explicit IDs beyond its current value.

Key Takeaways / Q&A

Q: Identity Column vs Sequence — when would you reach for a raw Sequence instead of an identity column? A: When you need one counter shared across multiple tables/columns (e.g., a single global document-number space), or when you need fine control accessible outside of any one column's default (calling nextval() explicitly from application logic before an insert, for example).

Q: Does rolling back a transaction restore a sequence value consumed by nextval() inside it? A: No — sequence advancement is not undone by rollback in PostgreSQL, which is why gaps appear after failed transactions, exactly as with identity columns (since identity columns are usually sequences underneath).

Q: What's the danger of a CYCLE sequence? A: After wrapping around, it can reissue a value that's already in use by an old row, risking constraint violations or, without a uniqueness constraint, silent data collisions.