1<!--
2doc/src/sgml/ref/create_materialized_view.sgml
3PostgreSQL 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>
24CREATE 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 </refsect1>
52
53 <refsect1>
54  <title>Parameters</title>
55
56  <variablelist>
57   <varlistentry>
58    <term><literal>IF NOT EXISTS</literal></term>
59    <listitem>
60     <para>
61      Do not throw an error if a materialized view with the same name already
62      exists. A notice is issued in this case.  Note that there is no guarantee
63      that the existing materialized view is anything like the one that would
64      have been created.
65     </para>
66    </listitem>
67   </varlistentry>
68
69   <varlistentry>
70    <term><replaceable>table_name</replaceable></term>
71    <listitem>
72     <para>
73      The name (optionally schema-qualified) of the materialized view to be
74      created.
75     </para>
76    </listitem>
77   </varlistentry>
78
79   <varlistentry>
80    <term><replaceable>column_name</replaceable></term>
81    <listitem>
82     <para>
83      The name of a column in the new materialized view.  If column names are
84      not provided, they are taken from the output column names of the query.
85     </para>
86    </listitem>
87   </varlistentry>
88
89   <varlistentry>
90    <term><literal>USING <replaceable class="parameter">method</replaceable></literal></term>
91    <listitem>
92     <para>
93      This optional clause specifies the table access method to use to store
94      the contents for the new materialized view; the method needs be an
95      access method of type <literal>TABLE</literal>. See <xref
96      linkend="tableam"/> for more information.  If this option is not
97      specified, the default table access method is chosen for the new
98      materialized view. See <xref linkend="guc-default-table-access-method"/>
99      for more information.
100     </para>
101    </listitem>
102   </varlistentry>
103
104   <varlistentry>
105    <term><literal>WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
106    <listitem>
107     <para>
108      This clause specifies optional storage parameters for the new
109      materialized view; see
110      <xref linkend="sql-createtable-storage-parameters"/> in the
111      <xref linkend="sql-createtable"/> documentation for more
112      information.  All parameters supported for <literal>CREATE
113      TABLE</literal> are also supported for <literal>CREATE MATERIALIZED
114      VIEW</literal>.
115      See <xref linkend="sql-createtable"/> for more information.
116     </para>
117    </listitem>
118   </varlistentry>
119
120   <varlistentry>
121    <term><literal>TABLESPACE <replaceable class="parameter">tablespace_name</replaceable></literal></term>
122    <listitem>
123     <para>
124      The <replaceable class="parameter">tablespace_name</replaceable> is the name
125      of the tablespace in which the new materialized view is to be created.
126      If not specified, <xref linkend="guc-default-tablespace"/> is consulted.
127     </para>
128    </listitem>
129   </varlistentry>
130
131   <varlistentry>
132    <term><replaceable>query</replaceable></term>
133    <listitem>
134     <para>
135      A <xref linkend="sql-select"/>, <link linkend="sql-table">TABLE</link>,
136      or <xref linkend="sql-values"/> command.  This query will run within a
137      security-restricted operation; in particular, calls to functions that
138      themselves create temporary tables will fail.
139     </para>
140    </listitem>
141   </varlistentry>
142
143   <varlistentry>
144    <term><literal>WITH [ NO ] DATA</literal></term>
145    <listitem>
146     <para>
147      This clause specifies whether or not the materialized view should be
148      populated at creation time.  If not, the materialized view will be
149      flagged as unscannable and cannot be queried until <command>REFRESH
150      MATERIALIZED VIEW</command> is used.
151     </para>
152    </listitem>
153   </varlistentry>
154
155  </variablelist>
156 </refsect1>
157
158 <refsect1>
159  <title>Compatibility</title>
160
161  <para>
162   <command>CREATE MATERIALIZED VIEW</command> is a
163   <productname>PostgreSQL</productname> extension.
164  </para>
165 </refsect1>
166
167 <refsect1>
168  <title>See Also</title>
169
170  <simplelist type="inline">
171   <member><xref linkend="sql-altermaterializedview"/></member>
172   <member><xref linkend="sql-createtableas"/></member>
173   <member><xref linkend="sql-createview"/></member>
174   <member><xref linkend="sql-dropmaterializedview"/></member>
175   <member><xref linkend="sql-refreshmaterializedview"/></member>
176  </simplelist>
177 </refsect1>
178
179</refentry>
180