add-column-identity
errorADD COLUMN GENERATED AS IDENTITY
Why it's unsafe
ADD COLUMN ... GENERATED AS IDENTITY creates a sequence and rewrites every existing row under an ACCESS EXCLUSIVE lock.
Safe rewrite
Add a plain nullable integer column, backfill existing rows in batches, then attach the identity/sequence — do not add an identity column directly to a populated table.
Example
Try it in the playground →Unsafe
ALTER TABLE users ADD COLUMN n int GENERATED ALWAYS AS IDENTITY;Safe
-- add a plain nullable column (no rewrite)
ALTER TABLE users ADD COLUMN n bigint;
-- backfill existing rows in batches and set NOT NULL (safe two-step), then:
ALTER TABLE users ALTER COLUMN n ADD GENERATED ALWAYS AS IDENTITY;