CREATE 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

返回 SELECT 命令标签，因为最近执行的命令是 SELECT，它作为物化视图定义的一部分存储

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;

复制源查询中的数据

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

即使底层表中的行被删除，数据也会保留

postgres=# DELETE FROM foo;
DELETE 1

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

可以基于另一个物化视图创建物化视图

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

无法向物化视图插入数据

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

可以创建未填充的物化视图，但无法查询

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 文档： CREATE MATERIALIZED VIEW