| 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> |