1CREATE MATERIALIZED VIEW(7P)ostgreSQL 14.3 DocumentatiCoRnEATE MATERIALIZED VIEW(7)
2
3
4

NAME

6       CREATE_MATERIALIZED_VIEW - define a new materialized view
7

SYNOPSIS

9       CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
10           [ (column_name [, ...] ) ]
11           [ USING method ]
12           [ WITH ( storage_parameter [= value] [, ... ] ) ]
13           [ TABLESPACE tablespace_name ]
14           AS query
15           [ WITH [ NO ] DATA ]
16

DESCRIPTION

18       CREATE MATERIALIZED VIEW defines a materialized view of a query. The
19       query is executed and used to populate the view at the time the command
20       is issued (unless WITH NO DATA is used) and may be refreshed later
21       using REFRESH MATERIALIZED VIEW.
22
23       CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, except that it
24       also remembers the query used to initialize the view, so that it can be
25       refreshed later upon demand. A materialized view has many of the same
26       properties as a table, but there is no support for temporary
27       materialized views.
28
29       CREATE MATERIALIZED VIEW requires CREATE privilege on the schema used
30       for the materialized view.
31

PARAMETERS

33       IF NOT EXISTS
34           Do not throw an error if a materialized view with the same name
35           already exists. A notice is issued in this case. Note that there is
36           no guarantee that the existing materialized view is anything like
37           the one that would have been created.
38
39       table_name
40           The name (optionally schema-qualified) of the materialized view to
41           be created.
42
43       column_name
44           The name of a column in the new materialized view. If column names
45           are not provided, they are taken from the output column names of
46           the query.
47
48       USING method
49           This optional clause specifies the table access method to use to
50           store the contents for the new materialized view; the method needs
51           be an access method of type TABLE. See Chapter 61 for more
52           information. If this option is not specified, the default table
53           access method is chosen for the new materialized view. See
54           default_table_access_method for more information.
55
56       WITH ( storage_parameter [= value] [, ... ] )
57           This clause specifies optional storage parameters for the new
58           materialized view; see Storage Parameters in the CREATE TABLE
59           (CREATE_TABLE(7)) documentation for more information. All
60           parameters supported for CREATE TABLE are also supported for CREATE
61           MATERIALIZED VIEW. See CREATE TABLE (CREATE_TABLE(7)) for more
62           information.
63
64       TABLESPACE tablespace_name
65           The tablespace_name is the name of the tablespace in which the new
66           materialized view is to be created. If not specified,
67           default_tablespace is consulted.
68
69       query
70           A SELECT, TABLE, or VALUES command. This query will run within a
71           security-restricted operation; in particular, calls to functions
72           that themselves create temporary tables will fail.
73
74       WITH [ NO ] DATA
75           This clause specifies whether or not the materialized view should
76           be populated at creation time. If not, the materialized view will
77           be flagged as unscannable and cannot be queried until REFRESH
78           MATERIALIZED VIEW is used.
79

COMPATIBILITY

81       CREATE MATERIALIZED VIEW is a PostgreSQL extension.
82

SEE ALSO

84       ALTER MATERIALIZED VIEW (ALTER_MATERIALIZED_VIEW(7)), CREATE TABLE AS
85       (CREATE_TABLE_AS(7)), CREATE VIEW (CREATE_VIEW(7)), DROP MATERIALIZED
86       VIEW (DROP_MATERIALIZED_VIEW(7)), REFRESH MATERIALIZED VIEW
87       (REFRESH_MATERIALIZED_VIEW(7))
88
89
90
91PostgreSQL 14.3                      2022          CREATE MATERIALIZED VIEW(7)
Impressum