← all rules

Prefer identity over serial

Why it's unsafe

A serial column creates an implicitly-owned sequence with ownership and permission sharp edges, and is not SQL-standard.

Safe rewrite

Use an identity column — GENERATED BY DEFAULT AS IDENTITY — which owns its sequence cleanly and is SQL-standard. pgsafe autofixes serial in a CREATE TABLE (width-preserving); on ADD COLUMN no fix is offered (adding an identity column to a populated table rewrites it, same as serial).

Unsafe

CREATE TABLE t (id bigserial PRIMARY KEY);

Safe

CREATE TABLE t (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY);

Related

• add-column-serial — Adding a serial/bigserial column creates a sequence and rewrites every row under an ACCESS EXCLUSIVE lock.
• add-column-identity — Adding a GENERATED ... AS IDENTITY column creates a sequence and rewrites every row under an ACCESS EXCLUSIVE lock.
• prefer-bigint-primary-key — A small-integer primary key overflows its id space; use bigint or bigint GENERATED BY DEFAULT AS IDENTITY.