1<!--
2doc/src/sgml/ref/refresh_materialized_view.sgml
3PostgreSQL documentation
4-->
5
6<refentry id="SQL-REFRESHMATERIALIZEDVIEW">
7 <indexterm zone="sql-refreshmaterializedview">
8  <primary>REFRESH MATERIALIZED VIEW</primary>
9 </indexterm>
10
11 <refmeta>
12  <refentrytitle>REFRESH MATERIALIZED VIEW</refentrytitle>
13  <manvolnum>7</manvolnum>
14  <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18  <refname>REFRESH MATERIALIZED VIEW</refname>
19  <refpurpose>replace the contents of a materialized view</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23<synopsis>
24REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <replaceable class="PARAMETER">name</replaceable>
25    [ WITH [ NO ] DATA ]
26</synopsis>
27 </refsynopsisdiv>
28
29 <refsect1>
30  <title>Description</title>
31
32  <para>
33   <command>REFRESH MATERIALIZED VIEW</command> completely replaces the
34   contents of a materialized view.  To execute this command you must be the
35   owner of the materialized view.  The old contents are discarded.  If
36   <literal>WITH DATA</literal> is specified (or defaults) the backing query
37   is executed to provide the new data, and the materialized view is left in a
38   scannable state.  If <literal>WITH NO DATA</literal> is specified no new
39   data is generated and the materialized view is left in an unscannable
40   state.
41  </para>
42  <para>
43   <literal>CONCURRENTLY</literal> and <literal>WITH NO DATA</literal> may not
44   be specified together.
45  </para>
46 </refsect1>
47
48 <refsect1>
49  <title>Parameters</title>
50
51  <variablelist>
52   <varlistentry>
53    <term><literal>CONCURRENTLY</literal></term>
54    <listitem>
55     <para>
56      Refresh the materialized view without locking out concurrent selects on
57      the materialized view.  Without this option a refresh which affects a
58      lot of rows will tend to use fewer resources and complete more quickly,
59      but could block other connections which are trying to read from the
60      materialized view.  This option may be faster in cases where a small
61      number of rows are affected.
62     </para>
63     <para>
64      This option is only allowed if there is at least one
65      <literal>UNIQUE</literal> index on the materialized view which uses only
66      column names and includes all rows;  that is, it must not be an
67      expression index or include a <literal>WHERE</literal> clause.
68     </para>
69     <para>
70      This option may not be used when the materialized view is not already
71      populated.
72     </para>
73     <para>
74      Even with this option only one <literal>REFRESH</literal> at a time may
75      run against any one materialized view.
76     </para>
77    </listitem>
78   </varlistentry>
79
80   <varlistentry>
81    <term><replaceable class="PARAMETER">name</replaceable></term>
82    <listitem>
83     <para>
84      The name (optionally schema-qualified) of the materialized view to
85      refresh.
86     </para>
87    </listitem>
88   </varlistentry>
89  </variablelist>
90 </refsect1>
91
92 <refsect1>
93  <title>Notes</title>
94
95  <para>
96   While the default index for future
97   <xref linkend="SQL-CLUSTER">
98   operations is retained, <command>REFRESH MATERIALIZED VIEW</> does not
99   order the generated rows based on this property. If you want the data
100   to be ordered upon generation, you must use an <literal>ORDER BY</>
101   clause in the backing query.
102  </para>
103 </refsect1>
104
105 <refsect1>
106  <title>Examples</title>
107
108  <para>
109   This command will replace the contents of the materialized view called
110   <literal>order_summary</literal> using the query from the materialized
111   view's definition, and leave it in a scannable state:
112<programlisting>
113REFRESH MATERIALIZED VIEW order_summary;
114</programlisting>
115  </para>
116
117  <para>
118   This command will free storage associated with the materialized view
119   <literal>annual_statistics_basis</literal> and leave it in an unscannable
120   state:
121<programlisting>
122REFRESH MATERIALIZED VIEW annual_statistics_basis WITH NO DATA;
123</programlisting></para>
124 </refsect1>
125
126 <refsect1>
127  <title>Compatibility</title>
128
129  <para>
130   <command>REFRESH MATERIALIZED VIEW</command> is a
131   <productname>PostgreSQL</productname> extension.
132  </para>
133 </refsect1>
134
135 <refsect1>
136  <title>See Also</title>
137
138  <simplelist type="inline">
139   <member><xref linkend="sql-creatematerializedview"></member>
140   <member><xref linkend="sql-altermaterializedview"></member>
141   <member><xref linkend="sql-dropmaterializedview"></member>
142  </simplelist>
143 </refsect1>
144
145</refentry>
146