Contents
CREATE MATERIALIZED VIEW
A DDL command for defining a materialized view
CREATE MATERIALIZED VIEW
is a DDL command for defining a materialized view.
CREATE MATERIALIZED VIEW
was added in PostgreSQL 9.3.
Change history
- PostgreSQL 13
USING method
syntax added (commit 8586bf7e)
- PostgreSQL 9.5
CREATE MATERIALIZED VIEW IF NOT EXISTS
syntax added (commit e39b6f95)
- PostgreSQL 9.3
- added (commit 3bf3ab8c)
Examples
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.
References
- PostgreSQL documentation: CREATE MATERIALIZED VIEW