Movatterモバイル変換

CREATE MATERIALIZED VIEW
Prev	Up	SQL Commands	Home	Next

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW — define a new materialized view

Synopsis

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ]table_name    [ (column_name [, ...] ) ]    [ USINGmethod ]    [ 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.

CREATE MATERIALIZED VIEW requiresCREATE privilege on the schema used for the materialized view.

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. The name must be distinct from the name of any other relation (table, sequence, index, view, materialized view, or foreign table) in the same schema.
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.
USINGmethod: This optional clause specifies the table access method to use to store the contents for the new materialized view; the method needs be an access method of typeTABLE. SeeChapter 59 for more information. If this option is not specified, the default table access method is chosen for the new materialized view. Seedefault_table_access_method for more information.
WITH (storage_parameter [=value] [, ... ] ): This clause specifies optional storage parameters for the new materialized view; seeStorage Parameters in theCREATE TABLE documentation for more information. All parameters supported forCREATE TABLE are also supported forCREATE MATERIALIZED VIEW. 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. Also, while the query is running, thesearch_path is temporarily changed topg_catalog, pg_temp.
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.

Compatibility

CREATE MATERIALIZED VIEW is aPostgres Pro extension.