Skip to main content

PG.L6.SERIAL_NOT_IDENTITY — A key generated by serial rather than by an identity column

  • Category: idiom
  • Level: 6
  • Confidence: deterministic
  • Downtime class: none — nothing here is a schema change
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL

serial is not a type, which is why no type check finds it​

id bigserial looks like a type declaration. It is a macro, and it expands into four separate objects:

CREATE SEQUENCE orders_id_seq;
CREATE TABLE orders (id bigint NOT NULL DEFAULT nextval('orders_id_seq'));
ALTER SEQUENCE orders_id_seq OWNED BY orders.id;

Afterwards the catalog reports the column as bigint — character for character what bigint GENERATED BY DEFAULT AS IDENTITY reports. The construction leaves its signature only in pg_depend, as an auto dependency from the sequence to the column.

That separateness is the whole issue, and it shows up in ordinary work rather than in theory.

The default is detachable, and nothing records that it was there​

ALTER TABLE orders ALTER COLUMN id DROP DEFAULT;

One statement, no error, and the column keeps every other property of a key: NOT NULL, primary key, bigint. It has simply stopped generating values, and the next insert without an explicit id fails with a null-violation on a column nobody touched. Nothing in the schema afterwards says this used to be a serial.

An identity column cannot be undone by accident. Removing its generation takes a statement that says what it is doing:

ALTER TABLE orders ALTER COLUMN id DROP IDENTITY;

The sequence carries its own permissions​

This is the single most common serial surprise, and it appears only at runtime, under a role that is not the owner:

ERROR:  permission denied for sequence orders_id_seq

A role granted INSERT on orders still cannot insert, because the default calls nextval on a separate object it was never granted USAGE on. Applications that run migrations as one role and serve traffic as another meet this on the first insert after a deploy.

An identity column's sequence is an internal dependency. There is no second grant, because there is no second object to grant.

An explicit value is accepted, and collides later​

serial produces at most the equivalent of GENERATED BY DEFAULT: a default applies only when no value is supplied, so an explicit id is taken as given, the sequence is not advanced, and nothing is reported.

INSERT INTO orders (id, total) VALUES (500, 19.99);   -- accepted; sequence still at 1

The collision arrives whenever the sequence eventually reaches 500 — on a row nobody was writing at the time, as a duplicate-key error with no visible cause. GENERATED ALWAYS AS IDENTITY refuses the explicit value outright, which is the behavior most people believed they already had.

Flagged​

Schema::create('orders', function (Blueprint $table) {
    $table->bigIncrements('id');
});

Preferred​

Schema::create('orders', function (Blueprint $table) {
    $table->id();
});

Laravel's id() produces an identity column on PostgreSQL; bigIncrements() produces a serial. For a table that already exists, the conversion is three statements plus a cleanup, and the middle one is the one people forget — without it the new identity sequence starts at 1 and collides with every row already there:

ALTER TABLE orders ALTER COLUMN id DROP DEFAULT;
ALTER TABLE orders ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY;
SELECT setval(pg_get_serial_sequence('orders', 'id'), (SELECT max(id) FROM orders));
DROP SEQUENCE orders_id_seq;   -- the old one, now referenced by nothing

All four are catalog-only operations: no table is rewritten and no row is touched.

Three neighbors this refuses to confuse with a serial​

The detection joins three catalog facts — the identity marker, the default's shape, and the dependency — because three ordinary, deliberate shapes sit right next to serial and each one would be a false positive. Measured on PostgreSQL 18.4:

ShapeattidentityDefaultpg_dependReported
bigserialemptynextval('t_id_seq')a on this columnyes
GENERATED … AS IDENTITYa / dnone at alli on this columnno
A shared or hand-managed sequenceemptynextval('s')nothing on this columnno
A sequence owned only for cleanupemptynonea on this columnno

The third row is worth stating twice. A sequence somebody created by hand and defaults several columns from is a design, not a serial that got away — and the dependency that proves it sits on the owning column, which may be a sibling on the same table. A check asking "does this table own a sequence" would report the borrowing column; this asks about the column.

What is answered as undetermined​

A column that owns its sequence but draws from it through a default that is not the bare call:

ALTER TABLE orders ALTER COLUMN id SET DEFAULT COALESCE(nextval('orders_id_seq'), 1);

Both answers would be wrong here. An identity column cannot express that default, so recommending the conversion would silently change what the column does; and staying quiet would report the table as checked when the one question worth asking went unanswered. The finding says so, with the reason sequence_ownership_unclear.

Why this is level 6 and not a defect​

serial works. It has worked for two decades, Laravel's bigIncrements() still produces it, and a schema full of it is not broken — which is exactly what level 6 is for. The finding is an invitation to decide, not a verdict, and a project that decides to keep serial has decided correctly for itself.

Sources​

  • PostgreSQL 18: serial types — states that serial is not a true type but a notational convenience that expands into a column, a sequence and a default, that the sequence is marked as owned by the column, and that a role needs USAGE or SELECT on the sequence in addition to its privileges on the table.
  • PostgreSQL 18: CREATE TABLE, identity columns — GENERATED ALWAYS rejects an explicit user value unless OVERRIDING SYSTEM VALUE is given, while GENERATED BY DEFAULT accepts it.