Postgres add column with Schema Diff Studio
The editors are loaded with a users table before and after a feature adds four columns, a foreign key and an index. The comparison runs on load in a real Postgres engine inside your tab, and the Changes tab shows the ALTER TABLE statements in a safe order, verified against the After schema. The rollback script and .sql downloads are Pro.
- Replace Before with your current schema: pg_dump --schema-only --table=users your_db works as it comes, or paste the CREATE TABLE from your last migration.
- Replace After with the table as it should be, with the new columns written inline the way you would design them.
- Press Compare (Ctrl/Cmd+Enter). The Changes tab lists each new column with its type, default, NULL rule and constraints.
- Open Migration and read the order: ADD COLUMN statements first, then CHECK and FOREIGN KEY constraints, then CREATE INDEX. Check that it says Verified.
- Copy the migration and adapt the risky lines for a large production table (see the notes below) before running it. Downloading the up and down .sql files is Pro.
What to know
Adding a column is cheap or expensive in Postgres depending on its default. A nullable column without a default only changes the catalog, whatever the table size. Since Postgres 11 a column with a constant or stable default, such as DEFAULT 'free', DEFAULT true or DEFAULT now(), is also instant: the value is stored once in the catalog and returned for old rows on read. A volatile default such as DEFAULT gen_random_uuid() or DEFAULT clock_timestamp() still rewrites the whole table under an ACCESS EXCLUSIVE lock, so on a large table add the column without a default, backfill in batches, then set the default.
NOT NULL on a new column only works in one statement when the column also has a default; ADD COLUMN x text NOT NULL without one fails as soon as the table has rows. When there is no sensible default, add the column as nullable, backfill it, add CHECK (x IS NOT NULL) NOT VALID, run VALIDATE CONSTRAINT (which takes a lighter lock), and only then SET NOT NULL, which Postgres 12+ can apply without a full scan because the validated check already proves it.
Every ALTER TABLE takes an ACCESS EXCLUSIVE lock, even the instant ones, and it waits behind any long-running query on the table while blocking everything queued after it. Set lock_timeout (for example SET lock_timeout = '3s') in the migration session so a busy table makes the migration fail fast and retry instead of stalling your traffic. Foreign keys deserve the same care: ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID followed by VALIDATE CONSTRAINT avoids holding the strong lock while existing rows are checked.
The generated script creates indexes with a plain CREATE INDEX, which blocks writes to the table while it builds. On a busy production table change it to CREATE INDEX CONCURRENTLY and run it outside a transaction block, since Postgres refuses CONCURRENTLY inside BEGIN/COMMIT. The verification here shows that the end state matches After; how you schedule each statement against live traffic is a separate decision, and the rollback script (Pro) is there for the moment it goes wrong.
Updated · Cosmovex
Questions
Does adding a column with a default lock or rewrite a large Postgres table?
On Postgres 11 and later, not if the default is a constant or a stable expression such as now(): the change is catalog-only and finishes in milliseconds, though it still needs a brief ACCESS EXCLUSIVE lock. A volatile default such as gen_random_uuid() rewrites every row, so add the column without it, backfill in batches and set the default afterwards.
How do I add a NOT NULL column to a table that already has rows?
Give it a default in the same statement (ADD COLUMN plan text NOT NULL DEFAULT 'free'), or add it as nullable, backfill, validate a CHECK (plan IS NOT NULL) constraint and then SET NOT NULL. Without either, Postgres rejects the ALTER because existing rows would violate the constraint.
What does Verified mean on the migration?
The tool creates a fresh Postgres database in your browser, runs your Before schema, applies the generated migration and reads the catalog back. Verified means the result matches After exactly: same columns, types, defaults, constraints and indexes. If anything differs, it lists what.
Can I paste pg_dump output?
Yes. pg_dump --schema-only output works as it comes; SET lines, OWNER TO, GRANT and psql meta-commands are set aside and listed so you can see nothing was silently dropped. Use --table=users to limit the dump to the tables you are changing.
Is my schema uploaded anywhere?
No. Both schemas run in a Postgres engine compiled to WebAssembly inside this tab, and projects are saved in this browser's storage only. Only the optional Pro share link uploads the schema text, and only when you turn it on.
The free plan covers everything on this page. Schema Diff Studio Pro ($29, paid once) is described on the Schema Diff Studio page.
More ways to use Schema Diff Studio
- Schema Diff Studio, no preset
- Compare pg_dump files
- Postgres rename column
- Postgres ER diagram
- Postgres add index
- SQLite add column
- Postgres enum change
- SQLite drop column
- Postgres add foreign key
- SQLite ER diagram
- Postgres change column type
- Postgres add constraints
