CREATE MATERIALIZED VIEW
Synopsis
CREATE MATERIALIZED VIEWtable_name [ (column_name [, ...] ) ] [ WITH (storage_parameter [=value] [, ... ] ) ] [ TABLESPACEtablespace_name ] ASquery [ 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 (unlessWITH NO DATA is used) and may be refreshed later usingREFRESH MATERIALIZED VIEW.
CREATE MATERIALIZED VIEW is similar toCREATE 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
- 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; seeStorage Parameters for more information. All parameters supported forCREATE TABLE are also supported forCREATE MATERIALIZED VIEW with the exception ofOIDS. SeeCREATE TABLE for more information.
- TABLESPACEtablespace_name
Thetablespace_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
ASELECT,TABLE, orVALUES 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 untilREFRESH MATERIALIZED VIEW is used.