This topic describes how to create 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]
Parameters
Parameter | Description |
---|---|
name | The name of a 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 SELECT. |
build clause | Include a build_clause to specify when the view is populated. Specify BUILD IMMEDIATE,
or BUILD DEFERRED:
|
create mv refresh | Include the create mv refresh clause to specify when the contents of a materialized
view must be updated. The clause contains the REFRESH keyword followed by COMPLETE
and/or ON DEMAND, where:
|
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 executing the CREATE MATERIALIZED VIEW statement, the view will be created in the specified schema. The view name must be distinct from the name of any other view, table, sequence, or index in the same schema.
- Materialized views are read-only. The server will not allow an INSERT, UPDATE, or DELETE operation on a view.
- Access to tables referenced in the view is determined by permissions of the view owner. The user of a view must have permissions to call all functions used by the view.
- For more information about the Postgres REFRESH MATERIALIZED VIEW statement, see the PostgreSQL Core Documentation.
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;