What exactly changes when you run ALTER TABLE ... RENAME COLUMN, and what keeps using the old name?

middleshould knowfreq 38%

answer

only one thing in the catalog is edited
the rows are never touched
indexes and constraints come along
the world outside the database does not

basics

~20 s

A rename edits the catalog entry only: the stored rows, the column's type, defaults, constraints and indexes are untouched and simply follow the new name. Nothing outside the database follows — application code, saved queries and reports still reference the old name and break.

solid answer

~40 s

ALTER TABLE customers RENAME COLUMN phone TO phone_number changes one name in the catalog. No data is rewritten, and everything attached to the column — its data type, default, NOT NULL, the indexes and constraints that include it — stays attached, now under the new name. What does not follow is anything that stored the old name as text: application SQL, dashboards, saved reports, ETL jobs. Views are engine-dependent: PostgreSQL resolves a view's definition to internal object identities, so the view keeps working after a rename and its own output column keeps its original label, which can be surprising in both directions. There is no portable standard rename — PostgreSQL and MySQL use ALTER TABLE ... RENAME, while SQL Server has no such syntax and renames through the sp_rename procedure.

`ALTER TABLE customers RENAME COLUMN phone TO phone_number` changes one name in the catalog. No data is rewritten, and everything attached to the column — its data type, default, `NOT NULL`, the indexes and constraints that include it — stays attached, now under the new name. What does not follow is anything that stored the old name as text: application SQL, dashboards, saved reports, ETL jobs. Views are engine-dependent: PostgreSQL resolves a view's definition to internal object identities, so the view keeps working after a rename and its own output column keeps its original label, which can be surprising in both directions. There is no portable standard rename — PostgreSQL and MySQL use `ALTER TABLE ... RENAME`, while SQL Server has no such syntax and renames through the `sp_rename` procedure.

code

sql · 3 lines

sql

-- PostgreSQL, MySQL, SQLite
ALTER TABLE customers RENAME COLUMN phone TO phone_number;
ALTER TABLE customers RENAME TO clients;

go deeper

for a junior

Know both forms — RENAME TO for a table, RENAME COLUMN for a column — and that the operation touches only the name: no data is copied and the column keeps its type, default and constraints.

for a middle

Explain what binds to the object and therefore follows the rename (indexes, constraints, foreign keys pointing at a renamed table) versus what holds the name as text and therefore breaks, and note the engine differences including SQL Server's sp_rename.

for a senior

Show the two constructive uses — rename as a reversible deprecation marker, and the rename swap for an atomic table cutover — and be clear that in-place rename is not coordinated with deployed application code.

for a principal

Own the position that a rename is a contract change, not a cosmetic one: who is entitled to break the name, what notice consumers get, and whether a view is maintained as a stable façade so internal names can evolve freely.

What the statements look like

sql

ALTER TABLE customers RENAME COLUMN phone TO phone_number;
ALTER TABLE customers RENAME TO clients;

Both are catalog edits. The rows are not read, not rewritten and not moved; the table's contents are identical microseconds after the statement as before it. That is why a rename is the cheapest structural change available and the only one that is trivially reversible — run it again in the other direction.

What follows the new name

Everything that is stored as a reference to the object rather than as text naming it:

the column's data type, DEFAULT, and NOT NULL;
indexes that include the column — a unique index on phone is now a unique index on phone_number, still enforcing the same rule, no rebuild needed;
constraints over the column: CHECK, UNIQUE, primary and foreign keys;
for a table rename, foreign keys in other tables that reference it — they bind to the table object, so they keep working and now point at the new name.

Note that the names of those objects do not change with the column. An index called idx_customers_phone keeps its stale name until you rename it too, which is cosmetic but a real source of later confusion.

What does not follow

Anything holding the old identifier as a string:

application code and ORM mappings;
ad-hoc SQL saved in dashboards, notebooks and BI tools;
ETL jobs, export definitions and downstream schemas;
in engines that store routine and view bodies as unresolved text, those bodies too.

This asymmetry is the whole risk profile of a rename: the database is instantly consistent, and everything outside it is instantly wrong. A rename is a contract break dressed as a one-line migration.

Views: engine-dependent, and subtler than it looks

PostgreSQL parses a view at creation time and stores the resolved dependency, binding to the column's internal identity rather than its spelling. Consequences:

the view keeps working after the base column is renamed — nothing to fix;
the view's own output column name does not change, because it was fixed when the view was created. CREATE VIEW v AS SELECT phone FROM customers still returns a column called phone after the base column becomes phone_number.

That second point cuts both ways: it means a rename can be invisible to consumers reading through a view (useful — the view acts as a stable façade), and it means the view and the table now disagree about what the thing is called (confusing). Engines that store view bodies as text behave differently and may break outright or silently resolve to something else. Verify on your engine rather than assuming.

Portability

There is no portable, standard-defined rename. In practice:

PostgreSQL — ALTER TABLE t RENAME TO t2, ALTER TABLE t RENAME COLUMN a TO b.
MySQL — RENAME TABLE a TO b and ALTER TABLE a RENAME TO b; ALTER TABLE t RENAME COLUMN a TO b, and the older CHANGE COLUMN a b <full definition>, which renames but demands the entire column definition and drops whatever you omit.
SQLite — ALTER TABLE t RENAME TO t2 and ALTER TABLE t RENAME COLUMN a TO b.
SQL Server — no ALTER TABLE ... RENAME at all; use the system procedure: EXEC sp_rename 'dbo.customers.phone', 'phone_number', 'COLUMN'.

Because of that spread, a rename is one of the few DDL operations that a database-agnostic migration tool usually cannot express portably.

Using a rename constructively

Two patterns are worth knowing:

Deprecation marker. Renaming a column to phone__deprecated_20260820 makes every remaining reader fail loudly and immediately, keeps all the data, and is undone by one statement. It is a far better first step than a drop when you merely believe a column is unused.
Atomic swap. Build a new table alongside the old, then rename both in a single statement or transaction (ALTER TABLE t RENAME TO t_old; ALTER TABLE t_new RENAME TO t;) so readers see one shape or the other and never a half-built one. Whether that pair is atomic depends on whether the engine gives you transactional DDL.

In both, the value comes from the same property: a rename moves a name, not data — cheap, reversible, and entirely invisible to everything that does not read the catalog.

## What the statements look like ```sql ALTER TABLE customers RENAME COLUMN phone TO phone_number; ALTER TABLE customers RENAME TO clients; ``` Both are catalog edits. The rows are not read, not rewritten and not moved; the table's contents are identical microseconds after the statement as before it. That is why a rename is the cheapest structural change available and the only one that is trivially reversible — run it again in the other direction. ## What follows the new name Everything that is stored as a *reference to the object* rather than as *text naming it*: - the column's data type, `DEFAULT`, and `NOT NULL`; - indexes that include the column — a unique index on `phone` is now a unique index on `phone_number`, still enforcing the same rule, no rebuild needed; - constraints over the column: `CHECK`, `UNIQUE`, primary and foreign keys; - for a table rename, foreign keys in other tables that reference it — they bind to the table object, so they keep working and now point at the new name. Note that the *names of those objects* do not change with the column. An index called `idx_customers_phone` keeps its stale name until you rename it too, which is cosmetic but a real source of later confusion. ## What does not follow Anything holding the old identifier as a string: - application code and ORM mappings; - ad-hoc SQL saved in dashboards, notebooks and BI tools; - ETL jobs, export definitions and downstream schemas; - in engines that store routine and view bodies as unresolved text, those bodies too. This asymmetry is the whole risk profile of a rename: the database is instantly consistent, and everything outside it is instantly wrong. A rename is a contract break dressed as a one-line migration. ## Views: engine-dependent, and subtler than it looks PostgreSQL parses a view at creation time and stores the resolved dependency, binding to the column's internal identity rather than its spelling. Consequences: - the view keeps working after the base column is renamed — nothing to fix; - the view's own output column name does not change, because it was fixed when the view was created. `CREATE VIEW v AS SELECT phone FROM customers` still returns a column called `phone` after the base column becomes `phone_number`. That second point cuts both ways: it means a rename can be invisible to consumers reading through a view (useful — the view acts as a stable façade), and it means the view and the table now disagree about what the thing is called (confusing). Engines that store view bodies as text behave differently and may break outright or silently resolve to something else. Verify on your engine rather than assuming. ## Portability There is no portable, standard-defined rename. In practice: - **PostgreSQL** — `ALTER TABLE t RENAME TO t2`, `ALTER TABLE t RENAME COLUMN a TO b`. - **MySQL** — `RENAME TABLE a TO b` and `ALTER TABLE a RENAME TO b`; `ALTER TABLE t RENAME COLUMN a TO b`, and the older `CHANGE COLUMN a b <full definition>`, which renames but demands the entire column definition and drops whatever you omit. - **SQLite** — `ALTER TABLE t RENAME TO t2` and `ALTER TABLE t RENAME COLUMN a TO b`. - **SQL Server** — no `ALTER TABLE ... RENAME` at all; use the system procedure: `EXEC sp_rename 'dbo.customers.phone', 'phone_number', 'COLUMN'`. Because of that spread, a rename is one of the few DDL operations that a database-agnostic migration tool usually cannot express portably. ## Using a rename constructively Two patterns are worth knowing: 1. **Deprecation marker.** Renaming a column to `phone__deprecated_20260820` makes every remaining reader fail loudly and immediately, keeps all the data, and is undone by one statement. It is a far better first step than a drop when you merely *believe* a column is unused. 2. **Atomic swap.** Build a new table alongside the old, then rename both in a single statement or transaction (`ALTER TABLE t RENAME TO t_old; ALTER TABLE t_new RENAME TO t;`) so readers see one shape or the other and never a half-built one. Whether that pair is atomic depends on whether the engine gives you transactional DDL. In both, the value comes from the same property: a rename moves a name, not data — cheap, reversible, and entirely invisible to everything that does not read the catalog.

Does a unique index on the renamed column need to be rebuilt?
No. The index is attached to the column object, not to its spelling, so it continues to enforce the same rule under the new name with no rebuild and no data movement. Only the index's own name is now stale — `idx_customers_phone` on a column called `phone_number` — which is cosmetic but worth renaming for the next reader.
In PostgreSQL, what happens to a view that selects the renamed column?
It keeps working. PostgreSQL resolves a view's definition to internal object identities at creation time, so the base column's new spelling is followed automatically. The view's own output column keeps the label it had, so consumers reading through the view see no change at all — which makes a view a useful stable façade over a renamed column.
How would you rename a column with no window in which readers can break?
Do not rename in place. Add the new column, write to both from the application, backfill, migrate readers to the new name, then drop the old one — the expand-and-contract sequence. An in-place rename is atomic in the database but not coordinated with deployed code, so old and new application versions cannot both be correct across it.

saying these in an interview costs you the question

Thinks a rename rewrites or moves the table's rows
Expects indexes and constraints to need recreation afterwards
Assumes ALTER TABLE RENAME works on every engine
Believes dependent views always break, or never break
Treats a rename as safe because the database stays consistent

What exactly changes when you run ALTER TABLE ... RENAME COLUMN, and what keeps using the old name?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What the statements look like

What follows the new name

What does not follow

Views: engine-dependent, and subtler than it looks

Portability

Using a rename constructively

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions3 questions

▾related topics3 topics

What the statements look like

What follows the new name

What does not follow

Views: engine-dependent, and subtler than it looks

Portability

Using a rename constructively

answer

go deeper

context