Postgresql Rename Table without Updating View Definitions I've found out today that renaming tables in Postgresql also renames the VIEW definitions for Views that use the table I renamed automatically? Is there any way to turn this off?

So I rename the table with:

ALTER TABLE xyz RENAME TO abc;


And my VIEW defition for the sake of example is:

CREATE VIEW foo AS SELECT * FROM xyz;


Next thing I know after I rename the table, the definition for foo has changed to point to table abc.

I'm using Postgresql 8.4

--Updated: Dec 30, 2014 at 12:41pm--

Here is a test case to illustrate what I mean:

CREATE TABLE tmp_test_a (num integer);
CREATE VIEW v_tmp_test_a AS SELECT * FROM tmp_test_a;

-- Now look at what the VIEW is using (now is using tmp_test_a, which is what it should be using):
SELECT pg_get_viewdef('v_tmp_test_a'::regclass, false);

-- Now create a second table, and swap them.
CREATE TABLE tmp_test_b (num integer);
ALTER TABLE tmp_test_a RENAME TO tmp_test_c;
ALTER TABLE tmp_test_b RENAME TO tmp_test_a;
ALTER TABLE tmp_test_c RENAME TO tmp_test_b;

-- Now look at what the VIEW is using again (now is using tmp_test_b with an alias of tmp_test_a)
SELECT pg_get_viewdef('v_tmp_test_a'::regclass, false);

-- Cleanup
DROP VIEW v_tmp_test_a;
DROP TABLE tmp_test_a;
DROP TABLE tmp_test_b;
The simple answer - because views reference the OID and not the object name. The name is translated back in the internal function.

A view in PostgreSQL can be thought of as an empty table with a select rewrite rule. Using your example, the internal query tree for the rewrite rule is located in the pg_rewrite.ev_action table/column and there you'll see the references to the individual table and column OIDs.

What I'd recommend doing is enclose the table rename, and a CREATE OR REPLACE VIEW statement within a single transaction, that way everything becomes relatively seamless and the view is recreated pointing to the new table's OID.