postgresql - Postgres : How to alter the name of an MV without it affecting other MVs that reference it?

Question

Essentially my issue is that I have an MV that works like this

create MATERIALIZED VIEW fake_mv as select * from other_fake_mv;

I want to swap out other_fake_mv for another MV with the same definition. I'm attempting to do it by renaming the original mv to some temp name, and the mv I'm trying to swap in to the original name like so:

ALTER TABLE other_fake_mv rename TO other_fake_mv_temp;
ALTER TABLE other_fake_mv_backup rename TO other_fake_mv;

The issue is that fake_mv still references the original other_fake_mv (now named other_fake_mv_temp) instead of the new other_fake_mv (other_fake_mv_backup). How do I get around this or explicitly for the reference to point the name instead of the table?

Answer 1

If you are to do this frequently, you can define the query in a regular view and build the MV from this view. This way, you can change the view definition as you wish and will still be able to refresh the MV afterward.

For example, it could be

-- Create the view containing the query to be (frequently) updated
CREATE VIEW myView as SELECT a,b,c FROM table_1;

-- Create the MV using the view
CREATE MATERIALIZED VIEW fake_mv as SELECT a,b,c FROM myView;


-- Update the view to point to another table
CREATE OR REPLACE myView as SELECT a,b,c FROM table_2;

-- Now you can just refresh the MV, as its definition has not changed (it is still just reading the same view)
REFRESH MATERIALIZED VIEW fake_mv;