ALTER TABLE … RENAME TO changes the name of an existing PostgreSQL table without touching the data or permissions.
Renaming helps reflect new business logic, fix typos, merge schemas, or follow naming conventions without copying data. The ALTER TABLE … RENAME TO statement makes the change instantly and keeps all data intact.
Run ALTER TABLE current_name RENAME TO new_name;. You need ownership or super-user rights. The command is transactional—if it fails, nothing changes.
ALTER TABLE sales RENAME TO sales_archive;
The table sales becomes sales_archive. All rows, constraints, indexes, and ACLs move with it.
Foreign-key constraints, sequences owned by serial/bigserial columns, and views referencing the table by OID stay valid. Views/functions that hard-code the table name must be recreated.
ALTER TABLE [IF EXISTS] schema_old.table_old
RENAME TO table_new;
Parameters
IF EXISTS – skip error if the table is missing.schema_old.table_old – current qualified name.table_new – new unqualified name, unique inside the schema.Schedule during low traffic windows if downstream tools cache metadata. Wrap the rename and view/function refreshes in one transaction. Qualify table names to avoid search_path surprises. Announce changes in shared docs or Galaxy Collections.
Using DROP & CREATE instead of RENAME. This deletes data and privileges. Always use ALTER TABLE … RENAME TO.
Forgetting schema qualification. If you rely on search_path, you might rename the wrong table. Use ALTER TABLE analytics.sales RENAME TO sales_q1;.
-- Rename only if the table exists
ALTER TABLE IF EXISTS public.temp_users RENAME TO users_staging;
-- Rename inside a transaction with dependent view
BEGIN;
ALTER TABLE payments RENAME TO payments_2023;
CREATE OR REPLACE VIEW payments AS SELECT * FROM payments_2023;
COMMIT;
ALTER TABLE … RENAME COLUMN – rename a columnALTER SEQUENCE … RENAME TO – rename a sequenceALTER VIEW … RENAME TO – rename a viewPostgreSQL takes an Access Exclusive lock, but only for a moment. Reads continue; concurrent writes and DDL wait briefly.
Foreign keys update automatically. Views remain valid if they reference the table’s OID, but views/functions that hard-code the name must be recreated.
Yes. Qualify the current table name: ALTER TABLE old_schema.orders RENAME TO orders_2024;. The table stays in the same schema.
.avif){kind=logo}
