Sample Header Ad - 728x90

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