6

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 refreshed later upon demand.

As I understand refreshing a materialized view should have the same effect as recreate view as. But it is not what happens here.

Create a table with a single column

drop table if exists t cascade;

create table t (a int);

insert into t (a) values (1);

Create the materialized view

create materialized view mat_view_t as
select * from t ;

select * from mat_view_t;
 a 
---
 1

Now a column is added to the source table

alter table t add column b int;

\d t
       Table "public.t"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | integer |

And then the materialized view is refreshed

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? Is it the expected behaviour? If it is then I think the manual is misleading.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260

1 Answers1

5

SELECT * is expanded at execution time, just like with all similar operations (CREATE VIEW, CREATE TABLE AS)

The key word is "early binding" as opposed to "late binding". Postgres saves the list of columns present at execution time of SELECT *, columns added later are not included automatically. The query string itself is not saved, only the internal representation after expanding SELECT * and other stuff like resolving all identifiers.

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

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

The manual could be more explicit about it, but the comparison to the behavior of CREATE TABLE AS made it clear for me:

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Saving the macro processed query is very likely the right answer. But the phrase that makes it clear for you is exactly the one that makes it murky for me: _except that it also remembers the query used to initialize the view_ – Clodoaldo Neto Sep 24 '13 at 10:33
  • @ClodoaldoNeto: It certainly wouldn't hurt to be explicit about that in the manual. – Erwin Brandstetter Sep 24 '13 at 19:25