Name
CREATE MATERIALIZED VIEW -- define a new materialized viewSynopsis
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name [ (column_name [, ...] ) ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] AS query [ WITH [ NO ] DATA ]
Description
CREATE MATERIALIZED VIEW
defines a materialized view of a query. The query is executed and used to populate the view at the time the command is issued (unless WITH NO DATA
is used) and may be refreshed later using REFRESH MATERIALIZED VIEW
.
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. A materialized view has many of the same properties as a table, but there is no support for temporary materialized views or automatic generation of OIDs.
Parameters
IF NOT EXISTS
-
Do not throw an error if a materialized view with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing materialized view is anything like the one that would have been created.
table_name
-
The name (optionally schema-qualified) of the materialized view to be created.
column_name
-
The name of a column in the new materialized view. If column names are not provided, they are taken from the output column names of the query.
WITH (
storage_parameter
[=value
] [, ... ] )-
This clause specifies optional storage parameters for the new materialized view; see Storage Parameters for more information. All parameters supported for
CREATE TABLE
are also supported forCREATE MATERIALIZED VIEW
with the exception ofOIDS
. See CREATE TABLE for more information. TABLESPACE
tablespace_name
-
The
tablespace_name
is the name of the tablespace in which the new materialized view is to be created. If not specified, default_tablespace is consulted. query
-
A SELECT, TABLE, or VALUES command. This query will run within a security-restricted operation; in particular, calls to functions that themselves create temporary tables will fail.
WITH [ NO ] DATA
-
This clause specifies whether or not the materialized view should be populated at creation time. If not, the materialized view will be flagged as unscannable and cannot be queried until
REFRESH MATERIALIZED VIEW
is used.
Compatibility
CREATE MATERIALIZED VIEW
is a PostgreSQL extension.
Please login to continue.