Postgresql Rename Table without Updating View Definitions
12
votes
1
answer
7746
views
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;
Asked by Brandon
(223 rep)
Dec 30, 2014, 04:04 AM
Last activity: Nov 22, 2022, 06:53 PM
Last activity: Nov 22, 2022, 06:53 PM