Materialized view update does not include added column

Question

From the manual

CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, except that it also remembers the query used to initialize the view so that it can be updated later on demand.

As I understand it, updating a materialized view should have the same effect as re create view as . But that is not what is happening here.

Create a single column table

 drop table if exists t cascade; create table t (a int); insert into t (a) values (1); 

Create a Materialized View

 create materialized view mat_view_t as select * from t ; select * from mat_view_t; a --- 1 

Now the column is added to the source table.

 alter table t add column b int; \dt Table "public.t" Column | Type | Modifiers --------+---------+----------- a | integer | b | integer | 

And then the materialized view is updated

 refresh materialized view mat_view_t; select * from mat_view_t; a --- 1 \d mat_view_t Materialized view "public.mat_view_t" Column | Type | Modifiers --------+---------+----------- a | integer | 

Where is the new column located? Is this expected behavior? If so, I think the manual is misleading.

Answer 1

SELECT * expands at run time, as with all similar operations ( CREATE VIEW , CREATE TABLE AS )

The keyword is "early binding" rather than "late binding." Postgres maintains a list of columns that are present at the time of SELECT * execution; columns added later are not automatically included. The query string itself is not saved, only the internal representation after the SELECT * extension and other things, such as resolving all identifiers.

REFRESH MATERIALIZED VIEW never changes the definition of data, only data:

REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view.

The guidance may be more explicit, but a comparison with the behavior of CREATE TABLE AS made me understand:

CREATE MATERIALIZED VIEW similar to CREATE TABLE AS , except that it also remembers the query used to initialize the view.