CODEWITHSUNDEEP
postgresqlview
jackrabbithanna
jackrabbithanna

Reputation: 167

Postgres View, after alter table to change table name, View still queries it?

Using Postgres database. I have an existing table, and several existing Views that query that table. Call the table, 'contacts'.

I alter the table, changing the name to 'contacts_backup'. I then created a new table with the same name the older table used to have 'contacts'

Now it appears that if I query the existing views, the data is still retrieved from the renamed table, contacts_backup, and not the new table, 'contacts'.

Can this be? How can I update the Views to query the new table of the same name, and not the renamed contacts_backup?

My new table is actually a foreign table, but shouldn't the principle be the same? I was expecting the existing tables to query against the new table, not the old renamed one.

What is an efficient way to update the existing views to query from the new table?

Upvotes: 5

Views: 3212

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246878

This is because PostgreSQL does not store the view definition as an SQL string, but as a parsed query tree.

These parsed query trees don't contain the names of the referenced objects, but only their object identifier (oid), which does not change when you rename an object. The same is true for table columns. This all holds for foreign tables as well.

When you examine the view definition, for example with pg_get_viewdef, the parse tree is rendered as text, so you will see the changed names.

If you want to change the table that a view is referring to, the only solution is to either DROP the view and CREATE it again, or you can use CREATE OR REPLACE VIEW.

Upvotes: 9

Related Questions