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