1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
|
'\" t
.\" Title: CREATE MATERIALIZED VIEW
.\" Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\" Date: 2024
.\" Manual: PostgreSQL 16.3 Documentation
.\" Source: PostgreSQL 16.3
.\" Language: English
.\"
.TH "CREATE MATERIALIZED VIEW" "7" "2024" "PostgreSQL 16.3" "PostgreSQL 16.3 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
CREATE_MATERIALIZED_VIEW \- define a new materialized view
.SH "SYNOPSIS"
.sp
.nf
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] \fItable_name\fR
[ (\fIcolumn_name\fR [, \&.\&.\&.] ) ]
[ USING \fImethod\fR ]
[ WITH ( \fIstorage_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] ) ]
[ TABLESPACE \fItablespace_name\fR ]
AS \fIquery\fR
[ WITH [ NO ] DATA ]
.fi
.SH "DESCRIPTION"
.PP
\fBCREATE MATERIALIZED VIEW\fR
defines a materialized view of a query\&. The query is executed and used to populate the view at the time the command is issued (unless
\fBWITH NO DATA\fR
is used) and may be refreshed later using
\fBREFRESH MATERIALIZED VIEW\fR\&.
.PP
\fBCREATE MATERIALIZED VIEW\fR
is similar to
\fBCREATE TABLE AS\fR, except that it also remembers the query used to initialize the view, so that it can be refreshed later upon demand\&. A materialized view has many of the same properties as a table, but there is no support for temporary materialized views\&.
.PP
\fBCREATE MATERIALIZED VIEW\fR
requires
CREATE
privilege on the schema used for the materialized view\&.
.SH "PARAMETERS"
.PP
IF NOT EXISTS
.RS 4
Do not throw an error if a materialized view with the same name already exists\&. A notice is issued in this case\&. Note that there is no guarantee that the existing materialized view is anything like the one that would have been created\&.
.RE
.PP
\fItable_name\fR
.RS 4
The name (optionally schema\-qualified) of the materialized view to be created\&. The name must be distinct from the name of any other relation (table, sequence, index, view, materialized view, or foreign table) in the same schema\&.
.RE
.PP
\fIcolumn_name\fR
.RS 4
The name of a column in the new materialized view\&. If column names are not provided, they are taken from the output column names of the query\&.
.RE
.PP
USING \fImethod\fR
.RS 4
This optional clause specifies the table access method to use to store the contents for the new materialized view; the method needs be an access method of type
TABLE\&. See
Chapter\ \&63
for more information\&. If this option is not specified, the default table access method is chosen for the new materialized view\&. See
default_table_access_method
for more information\&.
.RE
.PP
WITH ( \fIstorage_parameter\fR [= \fIvalue\fR] [, \&.\&.\&. ] )
.RS 4
This clause specifies optional storage parameters for the new materialized view; see
Storage Parameters
in the
CREATE TABLE (\fBCREATE_TABLE\fR(7))
documentation for more information\&. All parameters supported for
CREATE TABLE
are also supported for
CREATE MATERIALIZED VIEW\&. See
CREATE TABLE (\fBCREATE_TABLE\fR(7))
for more information\&.
.RE
.PP
TABLESPACE \fItablespace_name\fR
.RS 4
The
\fItablespace_name\fR
is the name of the tablespace in which the new materialized view is to be created\&. If not specified,
default_tablespace
is consulted\&.
.RE
.PP
\fIquery\fR
.RS 4
A
\fBSELECT\fR,
\fBTABLE\fR, or
\fBVALUES\fR
command\&. This query will run within a security\-restricted operation; in particular, calls to functions that themselves create temporary tables will fail\&.
.RE
.PP
WITH [ NO ] DATA
.RS 4
This clause specifies whether or not the materialized view should be populated at creation time\&. If not, the materialized view will be flagged as unscannable and cannot be queried until
\fBREFRESH MATERIALIZED VIEW\fR
is used\&.
.RE
.SH "COMPATIBILITY"
.PP
\fBCREATE MATERIALIZED VIEW\fR
is a
PostgreSQL
extension\&.
.SH "SEE ALSO"
ALTER MATERIALIZED VIEW (\fBALTER_MATERIALIZED_VIEW\fR(7)), CREATE TABLE AS (\fBCREATE_TABLE_AS\fR(7)), CREATE VIEW (\fBCREATE_VIEW\fR(7)), DROP MATERIALIZED VIEW (\fBDROP_MATERIALIZED_VIEW\fR(7)), REFRESH MATERIALIZED VIEW (\fBREFRESH_MATERIALIZED_VIEW\fR(7))
|
