CREATE MATERIALIZED VIEW v15

Suggest edits

Name

CREATE MATERIALIZED VIEW — Define a new materialized view.

Synopsis

CREATE MATERIALIZED VIEW <name>
   [<build_clause>][<create_mv_refresh>] AS subquery

      Where <build_clause> is:

      BUILD {IMMEDIATE | DEFERRED}

      Where <create_mv_refresh> is:

      REFRESH [COMPLETE] [ON DEMAND]

Description

CREATE MATERIALIZED VIEW defines a view of a query that isn't updated each time the view is referenced in a query. By default, the view is populated when the view is created. You can include the BUILD DEFERRED keywords to delay populating the view.

A materialized view can be schema-qualified. If you specify a schema name when invoking the CREATE MATERIALIZED VIEW command, the view is created in the specified schema. The view name must be different from the name of any other view, table, sequence, or index in the same schema.

Parameters

name

The name (optionally schema-qualified) of a view to create.

subquery

A SELECT statement that specifies the contents of the view.

build_clause

Include a build_clause to specify when to populate the view. Specify BUILD IMMEDIATE or BUILD DEFERRED:

  • BUILD IMMEDIATE (the default) populates the view immediately.
  • BUILD DEFERRED populates the view later, during a REFRESH operation.

create_mv_refresh

Include the create_mv_refresh clause to specify when to update the contents of a materialized view. The clause contains the REFRESH keyword followed by optional COMPLETE and ON DEMAND keywords, where:

  • COMPLETE discards the current content and reloads the materialized view by executing the view's defining query when refreshing the materialized view.

  • ON DEMAND (the default) refreshes the materialized view on demand by calling the DBMS_MVIEW package or by calling the Postgres REFRESH MATERIALIZED VIEW statement.

Notes

Materialized views are read-only. The server doesn't allow an INSERT, UPDATE, or DELETE on a view.

Permissions of the view owner determine access to tables referenced in the view. The user of a view must have permissions to call all functions the view uses.

For more information about the Postgres REFRESH MATERIALIZED VIEW command, see the PostgreSQL core documentation.

Examples

This statement creates a materialized view named dept_30:

CREATE MATERIALIZED VIEW dept_30 BUILD IMMEDIATE AS SELECT * FROM emp WHERE deptno = 30;

The view contains information retrieved from the emp table about any employee that works in department 30.

← Prev

CREATE INDEX

Next →

CREATE PACKAGE