Two schemas in, safe ALTER migration out — with hazard warnings before anything hits production.

PgDesigner Diff dialog showing unsaved changes with DROP, ADD, ALTER badges and hazard warnings (DELETES_DATA, TABLE_REWRITE) in light theme

Stop writing ALTER by hand

Every migration tool generates ALTER TABLE. Few tell you that your ALTER COLUMN TYPE will lock the table for 20 minutes, or that ADD COLUMN NOT NULL without a DEFAULT will fail on existing rows. PgDesigner does both — generates the SQL and warns you about the risks.

How the diff engine works

Give it two .pgd models — old and new. The engine computes every structural change and emits safe ALTER statements in the correct dependency order.

Phase 1 — DROP

Reverse dependency order: FK → indexes → columns → tables. Nothing breaks because dependents are removed first.

Phase 2 — CREATE / ALTER

Forward dependency order: schemas → enums → tables → columns → PK → indexes → FK → comments. References always exist before they're needed.

Four ways to diff

Model vs Model

Edit your .pgd, get the migration script instantly.

DB vs Model

Pass a connection string as the old schema, or pg_dump → convert → compare with your model.

Git versions

Two versions of .pgd from git history → ALTER patch.

Live preview

Press Ctrl+D to see ALTER SQL for unsaved changes.

Hazard detection — know the risks before you deploy

Every generated ALTER is analyzed for operational hazards. The diff engine emits a comment with the hazard level and code directly before the SQL statement.

DELETES_DATA DROP TABLE, DROP COLUMN — permanent data loss, requires confirmation

TABLE_RECREATE Adding, removing, or changing PARTITION BY strategy — requires full table rebuild

COLUMN_RECREATE Regular ↔ generated or STORED ↔ VIRTUAL column — dropped and re-added, current values are replaced

TYPE_RECREATE Enum values removed or reordered — PostgreSQL can't alter that in place, the type must be re-created

TABLE_REWRITE ALTER COLUMN TYPE with incompatible cast, new expression of a stored generated column, SET LOGGED/UNLOGGED, ACCESS METHOD or TABLESPACE — may rewrite the entire table on disk

BACKFILL_REQUIRED ADD COLUMN NOT NULL without DEFAULT or IDENTITY — existing rows have no value

ENUM_ADD_VALUE New enum value — can't be used in the same transaction that adds it

UNNAMED_CONSTRAINT Constraint without a name in the old model — can't be dropped by name, name it first

DETACH_PARTITION Partition removed from list — will be detached from the parent table

REATTACH_PARTITION Partition bounds changed — DETACH then re-ATTACH with new bounds

Compatible type casts (varchar(N) → varchar(M), integer → bigint, smallint → integer) are recognized as safe and don't trigger TABLE_REWRITE.

Smart rename detection

Renamed an index, foreign key, UNIQUE or CHECK constraint? PgDesigner compares the definition (table, columns, method, expression) — not just the name. If the definition is identical, it emits ALTER INDEX … RENAME TO or ALTER TABLE … RENAME CONSTRAINT instead of a drop+add cycle. Only actual structural changes trigger recreation.

What gets diffed

Schemas, tables (including comment, PARTITION BY, storage parameters, UNLOGGED, access method and tablespace), columns (type, nullable, default, identity with sequence options, STORED and VIRTUAL generated expressions, comment, compression, storage), indexes, FK, PK, UNIQUE, CHECK constraints, enum types (CREATE, ADD VALUE in position), comments, and partitions (CREATE, DETACH, re-ATTACH on bound change, storage parameters). Expressions are compared by meaning, so the formatting PostgreSQL applies to a live database is not a change. Views and functions diff is planned for Phase 2.

CLI

pgdesigner diff old.pgd new.pgd
pgdesigner diff -o migration.sql old.pgd new.pgd

Press Ctrl+D in the UI for a live diff preview with hazard badges — red for dangerous, yellow for warnings.

DDL Generation → Schema Linter → CLI Reference →

PostgreSQL Schema Diff & Migrations