pg-delta: support serial and identity transition diffs

[cursor]

Context

PostgreSQL offers three ways to create auto-incrementing columns:

• serial / bigserial (legacy, creates an implicit sequence)
• GENERATED ALWAYS AS IDENTITY (SQL-standard, strict)
• GENERATED BY DEFAULT AS IDENTITY (SQL-standard, allows manual inserts)

Converting between these styles requires different DDL patterns:

• serial → IDENTITY: drop default, add identity
• IDENTITY → serial: drop identity, create sequence, set default
• ALWAYS ↔ BY DEFAULT: alter identity

pgschema was not tracking these transitions correctly.

pg-delta has sequence tests for serial columns but no tests for GENERATED AS
IDENTITY columns or for converting between serial and identity styles.

Reproduction SQL

CREATE SCHEMA test_schema;

CREATE TABLE test_schema.items (
    c1 int NOT NULL,
    c2 serial,
    c3 int GENERATED ALWAYS AS IDENTITY
);

Change to diff:

CREATE TABLE test_schema.items (
    c1 serial NOT NULL,
    c2 int GENERATED ALWAYS AS IDENTITY,
    c3 int GENERATED BY DEFAULT AS IDENTITY
);

Expected DDL:

-- c1: add serial (create sequence + set default)
CREATE SEQUENCE test_schema.items_c1_seq OWNED BY test_schema.items.c1;
ALTER TABLE test_schema.items ALTER COLUMN c1 SET DEFAULT nextval('test_schema.items_c1_seq');

-- c2: serial → IDENTITY (drop default, drop sequence, add identity)
ALTER TABLE test_schema.items ALTER COLUMN c2 DROP DEFAULT;
DROP SEQUENCE test_schema.items_c2_seq;
ALTER TABLE test_schema.items ALTER COLUMN c2 ADD GENERATED ALWAYS AS IDENTITY;

-- c3: ALWAYS → BY DEFAULT
ALTER TABLE test_schema.items ALTER COLUMN c3 SET GENERATED BY DEFAULT;

How pgschema handled it

pgschema improved its sequence tracking to detect all three identity styles and
generate the correct transition DDL.

Current pg-delta status

Aspect	Status
Serial column sequence dependency	✅ Basic test
GENERATED ALWAYS AS IDENTITY	❌ No test
GENERATED BY DEFAULT AS IDENTITY	❌ No test
serial ↔ identity conversion	❌ No test
Identity column model fields	❓ Unknown

Comparison of approaches

	pgschema	pg-delta
Root cause	Incomplete identity column model	Missing identity column tests entirely
Fix scope	Sequence/column model + diff logic	Column model + ALTER serialiser
Complexity	High — three styles with distinct DDL	High — same complexity

Plan to handle it in pg-delta

1. Verify column model — check if pg_attribute.attidentity is captured
   in the table/column model (values: 'a' for ALWAYS, 'd' for BY DEFAULT,
   '' for none).
2. Add integration tests in tests/integration/sequence-operations.test.ts:
   • Create a table with GENERATED ALWAYS AS IDENTITY column
   • Create a table with GENERATED BY DEFAULT AS IDENTITY column
   • Test conversion: serial → IDENTITY
   • Test conversion: IDENTITY → serial
   • Test conversion: ALWAYS → BY DEFAULT
3. If needed, extend the column diff logic to detect identity changes and
   generate the correct ALTER statements:
   • ALTER TABLE ... ALTER COLUMN ... ADD GENERATED {ALWAYS|BY DEFAULT} AS IDENTITY
   • ALTER TABLE ... ALTER COLUMN ... SET GENERATED {ALWAYS|BY DEFAULT}
   • ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY