Features
RENAME TABLE changes an existing table’s name without touching its data or permissions.
ALTER TABLE … RENAME TO changes only the table name. Data blocks, sort keys, distribution style, grants, and statistics stay untouched, so queries switch seamlessly after you update their references.
Rename tables when a feature ships, a data model evolves, or a typo slips through. It keeps naming consistent while avoiding the overhead of creating a new table and copying data.
Use ALTER TABLE schema.old_name RENAME TO new_name; Run it in a transaction block to guarantee atomicity with downstream updates.
BEGIN;
ALTER TABLE public.orders RENAME TO customer_orders;
COMMIT;
Views, functions, and queries that hard-code the old table name break. Update them or use late-binding views. Foreign keys remain valid because they reference OIDs, not names.
Amazon Redshift takes a metadata lock that blocks concurrent DDL but allows reads and writes to continue. The lock usually lasts milliseconds.
1) Qualify the schema to avoid collisions. 2) Use transactions. 3) Update BI tools and ORMs immediately. 4) Search Git repos for the old name.
Forgetting the schema qualifier: Redshift searches the search_path and may rename the wrong table. Always specify schema.table.
Assuming views auto-update: Views store the table name literally. Refresh or recreate them after renaming.
No. Use ALTER TABLE … SET SCHEMA first, then RENAME TO.
Yes. Run ALTER TABLE new_name RENAME TO old_name. No data movement occurs.
No. Only catalog metadata changes, so it finishes instantly regardless of table size.
No. DROP the external table definition and CREATE it again with the new name instead.
.avif){kind=logo}
