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