CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW — Define a new materialized view

Synopsis

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name

[ (column_name [, ...] ) ]

[ USING method ]

[ 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 at the time the command is issued and is used to populate the view (unless WITH NO DATA is used), and it can be refreshed later using REFRESH MATERIALIZED VIEW.

CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, but it also remembers the query used to initialize the view, so that it can be refreshed later with a command. A materialized view has many of the same properties as a table, but temporary materialized views are not supported.

Parameters

IF NOT EXISTS

Do not throw an error if a materialized view with the same name already exists. A notice will be issued in this case. Note that this does not guarantee that the existing materialized view is at all similar to the one that would be created.

table_name

The name of the materialized view to be created (can be schema-qualified).

column_name

The name of a column in the new materialized view. If no column names are provided, they will be derived from the output column names of the query.

USING method

This optional clause specifies the table access method used to store the contents of the new materialized view; the method needs to be a TABLE-type access method. If this option is not specified, the default table access method is chosen for the new materialized view. See default_table_access_method for details.

WITH ( storage_parameter [= value] [, ... ] )

This clause specifies optional storage parameters for the new materialized view; see Storage Parameters in CREATE TABLE. All parameters supported by CREATE TABLE are also supported by CREATE MATERIALIZED VIEW. See CREATE TABLE for details.

TABLESPACE tablespace_name

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 be run in a security-restricted operation. In particular, calls to functions that would themselves create temporary tables will fail.

WITH [ NO ] DATA

This clause specifies whether the materialized view should be populated at creation time. If not, the materialized view will be marked as unscannable and cannot be queried until REFRESH MATERIALIZED VIEW is used.

See Also

ALTER MATERIALIZED VIEW, CREATE TABLE AS, CREATE VIEW , DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW