Debian Codegraph @

doc/src/sgml/ref/create_materialized_view.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_materialized_view.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-creatematerializedview">
7 <indexterm zone="sql-creatematerializedview">
8 <primary>CREATE MATERIALIZED VIEW</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE MATERIALIZED VIEW</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE MATERIALIZED VIEW</refname>
19 <refpurpose>define a new materialized view</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
25 [ (<replaceable>column_name</replaceable> [, ...] ) ]
26 [ USING <replaceable class="parameter">method</replaceable> ]
27 [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
28 [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
29 AS <replaceable>query</replaceable>
30 [ WITH [ NO ] DATA ]
31 </synopsis>
32 </refsynopsisdiv>
33
34 <refsect1>
35 <title>Description</title>
36
37 <para>
38 <command>CREATE MATERIALIZED VIEW</command> defines a materialized view of
39 a query. The query is executed and used to populate the view at the time
40 the command is issued (unless <command>WITH NO DATA</command> is used) and may be
41 refreshed later using <command>REFRESH MATERIALIZED VIEW</command>.
42 </para>
43
44 <para>
45 <command>CREATE MATERIALIZED VIEW</command> is similar to
46 <command>CREATE TABLE AS</command>, except that it also remembers the query used
47 to initialize the view, so that it can be refreshed later upon demand.
48 A materialized view has many of the same properties as a table, but there
49 is no support for temporary materialized views.
50 </para>
51
52 <para>
53 <command>CREATE MATERIALIZED VIEW</command> requires
54 <literal>CREATE</literal> privilege on the schema used for the materialized
55 view.
56 </para>
57 </refsect1>
58
59 <refsect1>
60 <title>Parameters</title>
61
62 <variablelist>
63 <varlistentry>
64 <term><literal>IF NOT EXISTS</literal></term>
65 <listitem>
66 <para>
67 Do not throw an error if a materialized view with the same name already
68 exists. A notice is issued in this case. Note that there is no guarantee
69 that the existing materialized view is anything like the one that would
70 have been created.
71 </para>
72 </listitem>
73 </varlistentry>
74
75 <varlistentry>
76 <term><replaceable>table_name</replaceable></term>
77 <listitem>
78 <para>
79 The name (optionally schema-qualified) of the materialized view to be
80 created. The name must be distinct from the name of any other relation
81 (table, sequence, index, view, materialized view, or foreign table) in
82 the same schema.
83 </para>
84 </listitem>
85 </varlistentry>
86
87 <varlistentry>
88 <term><replaceable>column_name</replaceable></term>
89 <listitem>
90 <para>
91 The name of a column in the new materialized view. If column names are
92 not provided, they are taken from the output column names of the query.
93 </para>
94 </listitem>
95 </varlistentry>
96
97 <varlistentry>
98 <term><literal>USING <replaceable class="parameter">method</replaceable></literal></term>
99 <listitem>
100 <para>
101 This optional clause specifies the table access method to use to store
102 the contents for the new materialized view; the method needs be an
103 access method of type <literal>TABLE</literal>. See <xref
104 linkend="tableam"/> for more information. If this option is not
105 specified, the default table access method is chosen for the new
106 materialized view. See <xref linkend="guc-default-table-access-method"/>
107 for more information.
108 </para>
109 </listitem>
110 </varlistentry>
111
112 <varlistentry>
113 <term><literal>WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
114 <listitem>
115 <para>
116 This clause specifies optional storage parameters for the new
117 materialized view; see
118 <xref linkend="sql-createtable-storage-parameters"/> in the
119 <xref linkend="sql-createtable"/> documentation for more
120 information. All parameters supported for <literal>CREATE
121 TABLE</literal> are also supported for <literal>CREATE MATERIALIZED
122 VIEW</literal>.
123 See <xref linkend="sql-createtable"/> for more information.
124 </para>
125 </listitem>
126 </varlistentry>
127
128 <varlistentry>
129 <term><literal>TABLESPACE <replaceable class="parameter">tablespace_name</replaceable></literal></term>
130 <listitem>
131 <para>
132 The <replaceable class="parameter">tablespace_name</replaceable> is the name
133 of the tablespace in which the new materialized view is to be created.
134 If not specified, <xref linkend="guc-default-tablespace"/> is consulted.
135 </para>
136 </listitem>
137 </varlistentry>
138
139 <varlistentry>
140 <term><replaceable>query</replaceable></term>
141 <listitem>
142 <para>
143 A <link linkend="sql-select"><command>SELECT</command></link>, <link linkend="sql-table"><command>TABLE</command></link>,
144 or <link linkend="sql-values"><command>VALUES</command></link> command. This query will run within a
145 security-restricted operation; in particular, calls to functions that
146 themselves create temporary tables will fail. Also, while the query is
147 running, the <xref linkend="guc-search-path"/> is temporarily changed to
148 <literal>pg_catalog, pg_temp</literal>.
149 </para>
150 </listitem>
151 </varlistentry>
152
153 <varlistentry>
154 <term><literal>WITH [ NO ] DATA</literal></term>
155 <listitem>
156 <para>
157 This clause specifies whether or not the materialized view should be
158 populated at creation time. If not, the materialized view will be
159 flagged as unscannable and cannot be queried until <command>REFRESH
160 MATERIALIZED VIEW</command> is used.
161 </para>
162 </listitem>
163 </varlistentry>
164
165 </variablelist>
166 </refsect1>
167
168 <refsect1>
169 <title>Compatibility</title>
170
171 <para>
172 <command>CREATE MATERIALIZED VIEW</command> is a
173 <productname>PostgreSQL</productname> extension.
174 </para>
175 </refsect1>
176
177 <refsect1>
178 <title>See Also</title>
179
180 <simplelist type="inline">
181 <member><xref linkend="sql-altermaterializedview"/></member>
182 <member><xref linkend="sql-createtableas"/></member>
183 <member><xref linkend="sql-createview"/></member>
184 <member><xref linkend="sql-dropmaterializedview"/></member>
185 <member><xref linkend="sql-refreshmaterializedview"/></member>
186 </simplelist>
187 </refsect1>
188
189 </refentry>