CREATE MATERIALIZED VIEW

Creating a materialized view:

postgres=# CREATE TABLE foo (id INT, val TEXT);
CREATE TABLE

postgres=# INSERT INTO foo VALUES(1, 'bar');
INSERT 0 1

postgres=# CREATE MATERIALIZED VIEW bar AS SELECT * FROM foo;
SELECT 1

A SELECT command tag is returned, as the command most recently performed is a SELECT, which is stored as part of the materialized view definition:

postgres=# \d+ bar
                              Materialized view "public.bar"
 Column |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------+-----------+----------+---------+----------+--------------+-------------
 id     | integer |           |          |         | plain    |              | 
 val    | text    |           |          |         | extended |              | 
View definition:
 SELECT foo.id,
    foo.val
   FROM foo;

The data from the source query is copied:

postgres=# SELECT * FROM bar;
 id | val 
----+-----
  1 | bar
(1 row)

The data is retained even if the rows from the underlying table(s) are removed:

postgres=# DELETE FROM foo;
DELETE 1

postgres=# SELECT * FROM bar;
 id | val 
----+-----
  1 | bar
(1 row)

It is possible to create a materialized view on top of another materialized view:

postgres=# CREATE MATERIALIZED VIEW bar2 AS SELECT * FROM bar;
SELECT 1

It is not possible to insert data into a materialized view:

postgres=# INSERT INTO bar values(2, 'baz');
ERROR:  cannot change materialized view "bar"

An unpopulated materialized view can be created, but not queried:

postgres=# CREATE MATERIALIZED VIEW bar3 AS SELECT * FROM foo WITH NO DATA;
CREATE MATERIALIZED VIEW

postgres=# SELECT * FROM bar3;
ERROR:  materialized view "bar3" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.

• PostgreSQL documentation: CREATE MATERIALIZED VIEW