prefer-identity-over-serial

What triggers it

A CreateStmt whose tableElts hold a ColumnDef whose typeName.names[].String.sval is one of serial, bigserial, smallserial, serial2, serial4, serial8. The comparison is against the exact lowered type name in the parse tree, not the SQL text. The loop returns on the first match, so a CREATE TABLE with four SERIAL columns reports one violation.

What does not

Anything under ctx.pgVersion < 10 — that guard returns null before the statement is even inspected. Any node that is not a CreateStmt: ALTER TABLE ... ADD COLUMN id SERIAL parses as AlterTableStmt and is MP015 territory, and CREATE TABLE ... AS SELECT parses as CreateTableAsStmt. A CreateStmt with no tableElts (a CREATE TABLE c PARTITION OF p ... child) returns null. GENERATED ALWAYS AS IDENTITY carries the underlying type name (int8, int4), so it never matches.

Where it applies

Applies to PostgreSQL 10 and later. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

No table lock is in play. CREATE TABLE takes ACCESS EXCLUSIVE on a relation nobody can reference yet, so nothing queues behind it. What the rule is really about is the object SERIAL creates on the side: an implicit sequence with its own owner and its own GRANTs, which DROP COLUMN does not remove and which a role holding INSERT on the table may still be unable to call nextval on.

Why it matters

SERIAL creates an implicit sequence with complex ownership rules. GENERATED ALWAYS AS IDENTITY (PG 10+) is SQL-standard, has clearer semantics, and prevents accidental manual inserts.

Unsafe, and safe

CREATE TABLE users (id SERIAL PRIMARY KEY);

CREATE TABLE users (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY);

What it assumes

The rule sees only the literal type name written in this statement. A column typed through a domain that wraps serial, or inherited via LIKE other_table INCLUDING DEFAULTS, is invisible to it. It cannot tell a deliberate SERIAL — one whose sequence grants are managed on purpose — from an accidental one. And because it returns on the first SERIAL column, the violation count understates how much of the table is affected.

What the CLI prints

⚠ [MP039] WARNING (line 1)
  Column "id" on "users" uses SERIAL. Use GENERATED ALWAYS AS IDENTITY instead (PG 10+, SQL-standard, cleaner ownership).

  Safe alternative:
  -- Use IDENTITY instead of SERIAL:
  -- "id" BIGINT GENERATED ALWAYS AS IDENTITY

  Why: SERIAL is a legacy shorthand that creates a separate sequence with confusing ownership semantics: dropping the column does not drop the sequence, and permissions are not automatically granted. GENERATED ALWAYS AS IDENTITY (PG 10+) is SQL-standard, has cleaner ownership, and is the recommended approach.
  Docs: https://migrationpilot.dev/rules/mp039

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP039
CREATE TABLE users (id SERIAL PRIMARY KEY);

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP039: false

# or keep it, and downgrade it
rules:
  MP039:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP015no-add-column-serialADD COLUMN with SERIAL has similar issues
• MP038prefer-bigint-over-intuse BIGINT for key columns
• MP045require-primary-keyevery table needs a primary key