Sequences and Identity Columns

What we're doing: Query a sequence directly, independent of any table, to show it is a genuine standalone object with its own state.

CREATE SEQUENCE orders_id_seq;
CREATE TABLE orders (id INT DEFAULT nextval('orders_id_seq') PRIMARY KEY, total NUMERIC);

INSERT INTO orders (total) VALUES (100), (50);

SELECT last_value FROM orders_id_seq;
SELECT nextval('orders_id_seq');  -- advances it further, with no INSERT involved at all

1–2

The sequence and the table are two separate objects — the column's DEFAULT is what connects them.

5

The sequence can be queried directly, like a table, showing its own current state.

6

nextval() advances the sequence even though no row was ever inserted — proving the sequence has state independent of orders.

last_value
------------
          2

nextval
---------
       3

Why this works: last_value confirms the sequence has already advanced to 2 after two inserts consumed values 1 and 2 — but the second query, calling nextval() directly with no INSERT anywhere near it, advances the sequence to 3 anyway, which is only possible because the sequence is a genuinely independent object with its own persistent state, not merely an internal counter attached to the orders table's row count.

What we're doing: Show GENERATED ALWAYS AS IDENTITY rejecting a plain explicit id, then succeeding only with OVERRIDING SYSTEM VALUE.

CREATE TABLE orders (id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, total NUMERIC);

INSERT INTO orders (total) VALUES (100);
-- succeeds -- id auto-assigned by the backing sequence

INSERT INTO orders (id, total) VALUES (999, 50);
-- rejected -- GENERATED ALWAYS refuses an explicit id by default

INSERT INTO orders (id, total) OVERRIDING SYSTEM VALUE VALUES (999, 50);
-- succeeds -- explicitly opted into overriding the identity behavior

3

Omitting id lets the backing sequence supply it, as expected.

6

Supplying id explicitly is rejected outright, since GENERATED ALWAYS means the database decides, by default.

9

OVERRIDING SYSTEM VALUE is the explicit, visible opt-in required to bypass that default.

INSERT 0 1

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

INSERT 0 1

Why this works: GENERATED ALWAYS is a deliberate stronger guarantee than SERIAL ever provided — it makes bypassing the sequence a visible, explicit act (OVERRIDING SYSTEM VALUE) rather than something that can happen accidentally by simply including the column in an INSERT's column list. This matters specifically for migration scripts or bulk-loading tools that might otherwise supply an id value out of habit, potentially creating id collisions with the sequence's future output — GENERATED ALWAYS catches that class of mistake at the database level instead of allowing it silently.

What we're doing: Reproduce a sequence gap directly by rolling back a transaction that consumed an id, then confirm the next insert skips that value permanently.

CREATE TABLE orders (id SERIAL PRIMARY KEY, total NUMERIC);

BEGIN;
INSERT INTO orders (total) VALUES (100);
ROLLBACK;

INSERT INTO orders (total) VALUES (200);

SELECT * FROM orders;

3–5

This transaction consumes id 1 via the sequence, then rolls back — the row is gone, but the sequence has already moved past 1.

7

This insert gets id 2, not id 1 — the sequence does not know or care that the previous consumer rolled back.

id | total
---+-------
 2 |   200
(1 row)

Why this works: The sequence advanced to 1 the instant nextval() was called during the first INSERT, entirely independent of whether that transaction eventually committed or rolled back — sequence state changes are not part of the transaction's undoable work, by design. This is exactly why gaps happen: the rollback undid the row insertion, but it could not and did not undo the sequence's advancement, since reverting that would require the sequence to somehow know no other concurrent transaction had already been handed a later value in the meantime.

What we're doing: Call nextval() directly inside a transaction that is then rolled back, and confirm the very next call continues forward rather than reverting.

CREATE SEQUENCE orders_id_seq;

BEGIN;
SELECT nextval('orders_id_seq') AS value_in_rolled_back_txn;
ROLLBACK;

SELECT nextval('orders_id_seq') AS value_after_rollback;

3–5

This transaction consumes a sequence value and then rolls back entirely.

7

The next call continues forward from the already-consumed value, not from before it — the rollback had no effect on the sequence.

value_in_rolled_back_txn
---------------------------
                         1

value_after_rollback
----------------------
                     2

Why this works: PostgreSQL treats sequence objects as living outside the normal transactional undo log specifically so that a nextval() call takes effect the instant it runs, visible to every other concurrent session immediately, rather than being provisional pending the calling transaction's outcome. If sequences behaved transactionally, two concurrent transactions each calling nextval() would create a genuine conflict: whichever one eventually rolled back would need its value "returned," but the other transaction might already be relying on having received a distinct, later value — the non-transactional design sidesteps this entire class of problem.

What we're doing: Simulate the max(id)+1 race by manually running both halves of the pattern from two "concurrent" sessions, showing the collision.

CREATE TABLE orders (id INT PRIMARY KEY, total NUMERIC);
INSERT INTO orders VALUES (5, 100);

-- Session A:
SELECT max(id) FROM orders;  -- reads 5, computes next = 6

-- Session B, running concurrently, before Session A's INSERT commits:
SELECT max(id) FROM orders;  -- ALSO reads 5 (Session A hasn't inserted yet), computes next = 6

-- Session A inserts:
INSERT INTO orders VALUES (6, 200);  -- succeeds

-- Session B inserts, using its own independently-computed value of 6:
INSERT INTO orders VALUES (6, 300);  -- fails: duplicate key

5

Session A reads max(id) = 5, before either session has inserted a new row.

8

Session B reads the SAME max(id) = 5, because Session A has not committed its insert yet — this is the actual race window.

11–15

Both sessions independently computed 6 as "the next id" — only one INSERT can succeed against a PRIMARY KEY.

INSERT 0 1

ERROR:  duplicate key value violates unique constraint "orders_pkey"
DETAIL:  Key (id)=(6) already exists.

Why this works: Both sessions' SELECT max(id) statements ran before either session's INSERT was visible to the other — this is not a hypothetical edge case, it is the normal, expected behavior of two genuinely concurrent transactions each reading committed data as it existed at the time of their read. Because computing "the next id" and inserting a row using it are two separate statements with a real time gap between them, any other transaction running the same two steps in that gap computes the identical "next" value — the PRIMARY KEY constraint here at least turns the collision into a visible error rather than silently allowing two different orders to share an id.