Renaming a Table with an Updatable View

The domain model says “account”, the database says user_accounts, and every new engineer asks why. Renaming the table is one statement and takes milliseconds. It also breaks, at the moment it commits, every query in every service that has not yet been redeployed with the new name — the web fleet mid-rollout, the billing worker that deploys weekly, the analyst’s saved report. PostgreSQL offers an unusually clean bridge: a view with the old name over the renamed table is automatically updatable, so old code can keep reading and writing through it without knowing anything changed, and the rename plus the view can be committed together so no query ever finds neither. This guide covers the exact statements, what does and does not work through the view, how ORMs cope, and when to remove it. It expands the procedure in Renaming and Splitting Tables Online.

Old and New Names, One Table Services still on the old code query user_accounts, which is now a simple view. Services on the new code query accounts, the renamed table. The view forwards SELECT, INSERT, UPDATE and DELETE to the table automatically. Indexes, constraints, triggers and sequences stay on the table. Old and New Names, One Table Old code FROM user_accounts user_accounts (view) SELECT * FROM accounts New code FROM accounts accounts (table) rows, indexes, triggers reads + writes reads + writes forwards
The view adds a name, not a copy — every read and write through it lands on the same rows.

Symptom / Error Signatures

Renames without a bridge fail immediately and loudly:

ERROR:  relation "user_accounts" does not exist
LINE 1: SELECT "user_accounts".* FROM "user_accounts" WHERE ...

With a bridge in place, the remaining errors point to gaps in it:

ERROR:  permission denied for view user_accounts                                          -- grants not copied
ERROR:  cannot insert into view "user_accounts"                                           -- view not simple
ERROR:  column "created_at" does not exist                                                -- view lists columns explicitly and a new column was added

Some ORMs also misbehave with views: code that introspects primary keys (for example to build RETURNING id or to support upsert) may not find a primary key on a view and fall back to different SQL.

Root Cause Analysis

PostgreSQL makes a view automatically updatable when it selects from exactly one table or updatable view, without DISTINCT, GROUP BY, aggregates, window functions, set operations or LIMIT, and each output column is a plain column reference. Inserts, updates and deletes against such a view are rewritten into the corresponding operations on the base table, including defaults, RETURNING clauses and row-level security. Triggers defined on the table fire as usual, because the operation really happens on the table.

The rename and the view creation are both fast catalog operations requiring ACCESS EXCLUSIVE briefly. Wrapped in one transaction, they become visible together: before commit, every query sees user_accounts as a table; after commit, it sees user_accounts as a view and accounts as the table.

Through the view Works? Note
SELECT, joins, filters yes planner inlines the view; no extra cost
INSERT ... RETURNING yes defaults from the table apply
UPDATE, DELETE yes row locks taken on the table
INSERT ... ON CONFLICT yes for simple views on current versions test your statements
TRUNCATE, ALTER TABLE, COPY ... FROM no target the table directly
LOCK TABLE user_accounts locks the view, not the table as expected update such code first
The Rename Transaction Sequence between the migration and PostgreSQL, with an old-code query arriving during it. The migration begins, renames the table, creates the view and grants, then commits. The old-code query that arrived during the transaction waits briefly on the lock, then runs against the view and succeeds. The Rename Transaction Migration PostgreSQL Old-code query BEGIN; SET LOCAL lock_timeout ALTER TABLE user_accounts RENAME TO accounts SELECT … FROM user_accounts (waits) CREATE VIEW user_accounts …; GRANT; COMMIT rows via the view
A query arriving mid-transaction waits milliseconds and then resolves the old name to the new view — it never sees the name missing.

Immediate Mitigation

If a rename already shipped without a bridge and old code is failing, create the view now:

1. Add the compatibility view with matching grants.

-- PostgreSQL · migration role · takes effect immediately for new queries
SET lock_timeout = '3s';
CREATE VIEW user_accounts AS SELECT * FROM accounts;
GRANT SELECT, INSERT, UPDATE, DELETE ON user_accounts TO app;
-- ROLLBACK PATH: DROP VIEW IF EXISTS user_accounts;

2. Copy every grant, not just the application’s. Reporting roles and service accounts need the same privileges on the view they had on the table.

-- PostgreSQL · read-only · grants to reproduce on the view
SELECT grantee, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.role_table_grants WHERE table_name = 'accounts'
GROUP BY grantee;

3. Check the remaining failure modes. Search application logs for errors that the view cannot absorb: TRUNCATE, COPY ... FROM, LOCK TABLE or ALTER TABLE statements that still use the old name must be changed to the new name, because they either fail against a view or do something different. Maintenance scripts and test fixtures are the usual culprits. If the table uses row-level security, create the view with WITH (security_invoker = true) on PostgreSQL 15+ so readers through the old name are filtered by their own policies rather than the view owner’s.

Permanent Fix / Long-Term Pattern

Plan the rename as three releases. Release 1 runs the rename and view creation in one transaction:

-- PostgreSQL · migration role · brief ACCESS EXCLUSIVE on the table
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE user_accounts RENAME TO accounts;
CREATE VIEW user_accounts AS SELECT * FROM accounts;
GRANT SELECT, INSERT, UPDATE, DELETE ON user_accounts TO app, reporting;
COMMIT;
-- ROLLBACK PATH: BEGIN; DROP VIEW user_accounts; ALTER TABLE accounts RENAME TO user_accounts; COMMIT;

Release 2 (and however many deploys it takes) moves every consumer to accounts. Release 3 drops the view once query statistics show no use for a full business cycle, and renames dependent objects for tidiness:

-- PostgreSQL · migration role · each a brief catalog change
SET lock_timeout = '3s';
DROP VIEW IF EXISTS user_accounts;
ALTER INDEX user_accounts_pkey RENAME TO accounts_pkey;
ALTER SEQUENCE user_accounts_id_seq RENAME TO accounts_id_seq;

Two cautions. SELECT * in a view is expanded at creation time, so columns added to accounts later do not appear through the view; if schema changes will continue during the transition, recreate the view after each (CREATE OR REPLACE VIEW can append columns). And test ORMs against the view before release 1 — most work transparently, but features that introspect keys or use TRUNCATE in tests need the real table name. MySQL supports updatable single-table views as well, and additionally offers atomic multi-table RENAME TABLE, covered in renaming a MySQL table atomically with RENAME TABLE. Column renames use the same idea at column level, as in renaming a column with expand and contract.

Three Releases for a Rename Five steps. Test the view with each consumer's query patterns; release 1 renames and creates the view in one transaction; release 2 migrates consumers to the new name; verify zero old-name queries; release 3 drops the view and renames dependent objects. Three Releases for a Rename STEP 1 Test the view ORM, writes, grants STEP 2 Rename + view one txn STEP 3 Migrate consumers service by service STEP 4 Verify unused stats show 0 STEP 5 Drop view rename indexes/seqs
The rename itself is the smallest step; the bridge's lifetime is set by the slowest consumer.

Verification Checklist

Frequently Asked Questions

Does a view slow down queries? No measurable amount for a simple view: the planner inlines it, so a query against the view is planned exactly as the same query against the table.

Do triggers on the table fire for writes through the view? Yes. Writes through an automatically updatable view are rewritten into writes on the table, and the table’s triggers, constraints and defaults apply as usual.

What happens to columns added after the view is created? They do not appear through the view, because SELECT * is expanded when the view is created. Recreate the view with CREATE OR REPLACE VIEW after adding columns if old-name consumers need them.

Can I rename the table and keep the view forever? You can, but it becomes a permanent second name that confuses tooling and future engineers. Treat the view as a transition aid with a removal date.

Related Articles