1CREATE MATERIALIZED VIEW(7P)ostgreSQL 15.4 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. The name must be distinct from the name of any other
42           relation (table, sequence, index, view, materialized view, or
43           foreign table) in the same schema.
44
45       column_name
46           The name of a column in the new materialized view. If column names
47           are not provided, they are taken from the output column names of
48           the query.
49
50       USING method
51           This optional clause specifies the table access method to use to
52           store the contents for the new materialized view; the method needs
53           be an access method of type TABLE. See Chapter 63 for more
54           information. If this option is not specified, the default table
55           access method is chosen for the new materialized view. See
56           default_table_access_method for more information.
57
58       WITH ( storage_parameter [= value] [, ... ] )
59           This clause specifies optional storage parameters for the new
60           materialized view; see Storage Parameters in the CREATE TABLE
61           (CREATE_TABLE(7)) documentation for more information. All
62           parameters supported for CREATE TABLE are also supported for CREATE
63           MATERIALIZED VIEW. See CREATE TABLE (CREATE_TABLE(7)) for more
64           information.
65
66       TABLESPACE tablespace_name
67           The tablespace_name is the name of the tablespace in which the new
68           materialized view is to be created. If not specified,
69           default_tablespace is consulted.
70
71       query
72           A SELECT, TABLE, or VALUES command. This query will run within a
73           security-restricted operation; in particular, calls to functions
74           that themselves create temporary tables will fail.
75
76       WITH [ NO ] DATA
77           This clause specifies whether or not the materialized view should
78           be populated at creation time. If not, the materialized view will
79           be flagged as unscannable and cannot be queried until REFRESH
80           MATERIALIZED VIEW is used.
81

COMPATIBILITY

83       CREATE MATERIALIZED VIEW is a PostgreSQL extension.
84

SEE ALSO

86       ALTER MATERIALIZED VIEW (ALTER_MATERIALIZED_VIEW(7)), CREATE TABLE AS
87       (CREATE_TABLE_AS(7)), CREATE VIEW (CREATE_VIEW(7)), DROP MATERIALIZED
88       VIEW (DROP_MATERIALIZED_VIEW(7)), REFRESH MATERIALIZED VIEW
89       (REFRESH_MATERIALIZED_VIEW(7))
90
91
92
93PostgreSQL 15.4                      2023          CREATE MATERIALIZED VIEW(7)
Impressum