Postgres change column type with Schema Diff Studio
A payments table is loaded before and after a type clean-up: integer keys and amounts become bigint, a varchar(120) becomes text, a timestamp stored as text becomes timestamptz and an external reference becomes uuid. The comparison runs on load and opens the Migration tab with one ALTER COLUMN ... TYPE ... USING per column, each flagged for review, verified in Postgres in your tab.
- Paste the current table into Before and the same table with the new types into After. Leave everything else as it is so only the type changes show.
- Press Compare. Each changed column appears as "Column x: type a → b", flagged REVIEW, because a type change can fail on existing values or rewrite the table.
- Open Migration: each line reads ALTER TABLE ... ALTER COLUMN ... TYPE new_type USING col::new_type. Edit a USING expression when a plain cast is not what you need, such as to_timestamp(paid_at, 'DD/MM/YYYY HH24:MI').
- Test the cast on real data before the migration: SELECT paid_at FROM payments WHERE paid_at IS NOT NULL AND paid_at !~ '^\d{4}-' LIMIT 20 finds values a plain cast would reject.
- Check Verified and copy the migration. Downloading the up and down .sql files and the full rollback script are Pro.
What to know
Whether a type change is instant or a full rewrite depends on the pair of types. varchar(120) to text, or raising a varchar limit, is binary-compatible: Postgres only updates the catalog. integer to bigint, text to timestamptz and text to uuid change how each value is stored, so Postgres rewrites the entire table and rebuilds its indexes under an ACCESS EXCLUSIVE lock, blocking reads and writes for the duration. On a table with hundreds of millions of rows that can be a long outage.
The USING clause is how each old value becomes a new one. A plain cast such as paid_at::timestamptz parses ISO 8601 strings, and the session TimeZone setting is applied to strings without an offset, so set it explicitly. A value that does not parse aborts the whole statement, with nothing changed. external_ref::uuid fails on any string that is not a valid UUID, and empty strings are not NULL: use NULLIF(external_ref, '')::uuid if the column contains them.
For an integer primary key that is running out of range (integer stops at 2,147,483,647), the zero-downtime route is the expand-and-contract pattern: add id_new bigint, keep it in sync with a trigger, backfill in batches, build a unique index concurrently, then swap the columns and the primary key in one short transaction. Remember the sequence too: a serial column's sequence is declared AS integer, so it also needs ALTER SEQUENCE payments_id_seq AS bigint, and every foreign key column that references the id needs the same type change.
Rolling back a type change is not always lossless. bigint back to integer fails for values above the integer range, timestamptz back to text produces a different string format than the original, and text back to varchar(120) fails on longer values. The rollback script (Pro) is verified against your Before schema, but whether your data survives the round trip depends on the values, so treat the down migration as a schema undo, not a data backup.
Updated · Cosmovex
Questions
Does ALTER COLUMN TYPE rewrite the table in Postgres?
It depends on the types. varchar to text, or a longer varchar limit, does not: only the catalog changes. integer to bigint, text to timestamptz, text to uuid and most other changes rewrite every row and rebuild the indexes under an ACCESS EXCLUSIVE lock.
Why does the migration need a USING clause?
When Postgres has no automatic assignment cast between the old and new type, as from text to timestamptz or uuid, it refuses the change and asks for USING. The generated line always includes USING col::new_type so the intent is explicit; edit it when the old values need parsing rather than a straight cast.
How do I change an integer primary key to bigint?
On a small table, ALTER COLUMN id TYPE bigint, plus the same change on every referencing column and ALTER SEQUENCE ... AS bigint for a serial column. On a large table, add a new bigint column, backfill it in batches, keep it in sync with a trigger, then swap it in during a short transaction, to avoid a long rewrite lock.
What happens to values that do not convert?
The whole ALTER statement fails and the table is left as it was. Find them before migrating, for example with a regular expression check on the text column, and fix them or write a USING expression that maps them, such as NULLIF(col, '')::uuid.
Is the change verified against real data?
No. Verification runs the migration on an empty database built from your Before schema and checks that the result matches After. It proves the SQL and its order are right; whether every existing value converts is a property of your data, which the tool never sees.
The free plan covers everything on this page. Schema Diff Studio Pro ($29, paid once) is described on the Schema Diff Studio page.
