ALTER TABLE RENAME
Rename a table or one of its columns. The data, indexes, and constraints stay in place; only the identifier changes. Cheap to run, surprisingly expensive to coordinate, because every dashboard, dbt model, view, and stored query that references the old name breaks the moment you commit.
Syntax #
-- Rename a table.
ALTER TABLE old_table_name RENAME TO new_table_name;
-- Rename a column.
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;Example #
Dialect notes #
- Older MySQL / Oracle:
ALTER TABLE t CHANGE old_col new_col data_type, where you must repeat the data type. Modern MySQL (8.0+) and Oracle (9i+) also acceptRENAME COLUMN. - SQLite: Column rename added in 3.25 (2018). Older versions need a full
CREATE TABLE new+INSERT INTO new SELECT FROM old+DROP olddance. - Schema-qualified rename: Most engines accept
ALTER TABLE schema.t RENAME TO new_t; you cannot rename into a different schema withRENAME— useALTER TABLE ... SET SCHEMA(Postgres) or a move-and-rename pattern.
The dependency footprint of a rename is invisible from the database alone. Views and stored procedures inside the database are auto-updated on most engines, but every dashboard query, BI metric, dbt ref, and application SQL string referring to the old name will silently break the next time it runs. Before renaming a production table: grep your codebase, search BI tool definitions, and consider creating a view with the old name pointing at the new table for a deprecation window.