1CREATE MATERIALIZED VIEW(7P)ostgreSQL 15.4 DocumentatiCoRnEATE MATERIALIZED VIEW(7)
2
3
4
6 CREATE_MATERIALIZED_VIEW - define a new materialized view
7
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
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
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
83 CREATE MATERIALIZED VIEW is a PostgreSQL extension.
84
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)