Replace materialized views

If the base table for a materialized view changes in a way that affects a materialized view for that table, users might see the following error for queries that use the view:
ERROR:  Base table/view 'WEATHER' attr 'CITY' has changed (precision); 
rebuild view 'WEATHER_V'

This error indicates that the column named CITY in the base table WEATHER changed. In this example, the column changed from a VARCHAR(80) to a VARCHAR(100). As a result, the materialized view must be rebuilt to reflect the current base table definition.

To rebuild a view after a base table change, use the CREATE OR REPLACE MATERIALIZED VIEW command to update the view, as follows:

MYDB.SCHEMA(ADMIN)=> CREATE OR REPLACE MATERIALIZED VIEW weather_v AS SELECT 
city, temp_lo, temp_hi FROM weather ORDER BY city;
CREATE MATERIALIZED VIEW

Do not drop and re-create the materialized view because those steps result in a new view with a different object ID, which can affect other objects that reference the materialized view.