Creates a materialized view.
Syntax
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 is not 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 the population of the view.
A materialized view can be schema-qualified. If you specify a schema name when running the CREATE MATERIALIZED VIEW command, the view is created in the specified schema. The view name must be different from the names of all other views, tables, sequences, and indexes in the same schema.
Access to tables referenced in the view is determined by privileges of the view owner. The user of a view must have privileges to call all functions used by the view.
For more information about the Postgres REFRESH MATERIALIZED VIEW
command, see the PostgreSQL documentation.
Parameters
Parameter | Description |
---|---|
name | The name of the view to be created. The name can be schema-qualified. |
subquery | A SELECT statement that specifies the contents of the view. For more information about valid queries, see the SELECT command. |
build_clause | Include a build_clause to specify when the view is populated. You can specify BUILD
IMMEDIATE or BUILD DEFERRED.
|
create_mv_refresh | Include the create_mv_refresh clause to specify when the content of a materialized
view is updated. The clause contains the REFRESH keyword followed by COMPLETE and/or
ON DEMAND, where:
|
Examples
The following 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 all employees that work in department 30.