Postgres rename column with Schema Diff Studio
A customers table is loaded before and after three columns were renamed: fullname to full_name, phone_no to phone_number and created to created_at. The comparison runs on load in Postgres inside your tab. Each pair is flagged as a probable rename, and one tick switches the migration from DROP plus ADD to RENAME COLUMN, then verifies it again.
- Paste the table as it is today into Before (pg_dump --schema-only --table=customers your_db, or the CREATE TABLE from your migrations) and the renamed version into After.
- Press Compare (Ctrl/Cmd+Enter). A column that disappeared next to a similar new one of the same type family is listed as "Probable rename" instead of being silently dropped.
- Tick "Use RENAME (keeps the data)" on each pair that really is a rename. The migration is regenerated with ALTER TABLE ... RENAME COLUMN and verified again against After.
- Leave a pair unticked when it is not a rename: the script then keeps the DROP COLUMN, marked DESTRUCTIVE, and the ADD COLUMN for the new one.
- Open Migration, check the index rename and the Verified badge, and copy the script. Downloading the up and down .sql files is Pro.
What to know
A schema diff only sees two states, so it cannot know whether fullname became full_name or whether one column was removed and an unrelated one added. Treating it as DROP plus ADD is the dangerous default: the migration runs cleanly and every value in the column is gone. This tool never guesses. It pairs a removed column with an added one when the names are similar and the types belong to the same family (text with text, numbers with numbers, dates with dates), flags the pair, and leaves the decision to you.
ALTER TABLE customers RENAME COLUMN fullname TO full_name only changes the catalog, so it finishes in milliseconds whatever the table size. It still needs an ACCESS EXCLUSIVE lock for that moment, and it waits behind any long transaction that touches the table while blocking every query queued after it. Run it with SET lock_timeout = '3s' so a busy table makes the migration fail and retry instead of stalling traffic.
Postgres follows a rename everywhere it stores references by column number: indexes, constraints, foreign keys and views keep working, and a view simply shows the new name. What it cannot follow is text: application queries, ORM mappings, PL/pgSQL function bodies and triggers written as strings still say fullname and fail on the next call. Index and constraint names are not renamed either, which is why the After schema here also renames customers_fullname_idx and the migration includes ALTER INDEX ... RENAME TO.
For a rename with no downtime, deploy it in steps instead of one statement: add full_name, write to both columns from the application, backfill old rows in batches, switch reads to full_name, then drop fullname in a later release. Each of those steps is an ordinary schema change you can compare here, one pair of schemas at a time.
Updated · Cosmovex
Questions
Why does a schema diff tool turn my rename into DROP COLUMN and ADD COLUMN?
Because two schemas alone do not say what happened between them. A diff sees one column missing and one new column. This tool still writes DROP plus ADD by default, so nothing is assumed, but it flags the pair as a probable rename and offers a ready RENAME COLUMN line that you switch on with one tick.
Does RENAME COLUMN lock or rewrite the table in Postgres?
It does not rewrite anything; it only updates the catalog. It does take an ACCESS EXCLUSIVE lock for that instant, so it has to wait for running queries on the table to finish. Set lock_timeout in the migration session so it gives up quickly instead of queuing your traffic behind it.
Do indexes, foreign keys and views follow the renamed column?
Yes. They reference the column by its internal number, so they keep working after the rename. Their own names do not change, though, so an index called customers_fullname_idx keeps that name until you rename it too. Function bodies and application SQL are plain text and must be updated by hand.
What if the tool pairs two columns that are not a rename?
Leave the box unticked. The migration then drops the old column (marked DESTRUCTIVE) and adds the new one, which is what you want when the old data really should go. Pairs are only suggested when the names are similar and the types are in the same family.
Can it detect a renamed table?
No. A renamed table is shown as one table dropped and another created. Write ALTER TABLE old_name RENAME TO new_name yourself, put that name in Before, and compare again to get the remaining column changes.
The free plan covers everything on this page. Schema Diff Studio Pro ($29, paid once) is described on the Schema Diff Studio page.
