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