doc/src/sgml/ref/create_materialized_view.sgml

<!--
doc/src/sgml/ref/create_materialized_view.sgml
PostgreSQL documentation
-->

<refentry id="sql-creatematerializedview">
 <indexterm zone="sql-creatematerializedview">
  <primary>CREATE MATERIALIZED VIEW</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE MATERIALIZED VIEW</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE MATERIALIZED VIEW</refname>
  <refpurpose>define a new materialized view</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE [ INCREMENTAL ] MATERIALIZED VIEW [ IF NOT EXISTS ] <replaceable>table_name</replaceable>
    [ (<replaceable>column_name</replaceable> [, ...] ) ]
    [ USING <replaceable class="parameter">method</replaceable> ]
    [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
    [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
    AS <replaceable>query</replaceable>
    [ WITH [ NO ] DATA ]
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>CREATE MATERIALIZED VIEW</command> defines a materialized view of
   a query.  The query is executed and used to populate the view at the time
   the command is issued (unless <command>WITH NO DATA</command> is used) and may be
   refreshed later using <command>REFRESH MATERIALIZED VIEW</command>.
  </para>

  <para>
   <command>CREATE MATERIALIZED VIEW</command> is similar to
   <command>CREATE TABLE AS</command>, except that it also remembers the query used
   to initialize the view, so that it can be refreshed later upon demand.
   A materialized view has many of the same properties as a table, but there
   is no support for temporary materialized views.
  </para>

  <para>
   <command>CREATE MATERIALIZED VIEW</command> requires
   <literal>CREATE</literal> privilege on the schema used for the materialized
   view.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><literal>INCREMENTAL</literal></term>
    <listitem>
     <para>
      If specified, some triggers are automatically created so that the rows
      of the materialized view are immediately updated when base tables of the
      materialized view are updated. In general, this allows faster update of
      the materialized view at a price of slower update of the base tables
      because the triggers will be invoked. We call this form of materialized
      view as "Incrementally Maintainable Materialized View" (IMMV).
     </para>
     <para>
      There are restrictions of query definitions allowed to use this
      option. The following are supported in query definitions for IMMV:
      <itemizedlist>

       <listitem>
        <para>
         Inner joins (including self-joins).
        </para>
       </listitem>

       <listitem>
        <para>
         Some built-in aggregate functions (count, sum, avg, min, max) without a HAVING
         clause.
        </para>
        </listitem>
      </itemizedlist>

      Unsupported queries with this option include the following:

      <itemizedlist>
       <listitem>
        <para>
         Outer joins.
        </para>
       </listitem>

       <listitem>
        <para>
         Sub-queries.
        </para>
       </listitem>

       <listitem>
        <para>
         Aggregate functions other than built-in count, sum, avg, min and max.
        </para>
       </listitem>
       <listitem>
        <para>
         Aggregate functions with a HAVING clause.
        </para>
       </listitem>
       <listitem>
        <para>
         DISTINCT ON, WINDOW, VALUES, LIMIT and OFFSET clause.
        </para>
       </listitem>
      </itemizedlist>

      Other restrictions include:
      <itemizedlist>

       <listitem>
        <para>
         IMMVs must be based on simple base tables. It's not supported to
         create them on top of views or materialized views.
        </para>
       </listitem>

       <listitem>
        <para>
         It is not supported to include system columns in an IMMV.
         <programlisting>
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm02 AS SELECT i,j FROM mv_base_a WHERE xmin = '610';
ERROR:  system column is not supported with IVM
         </programlisting>
        </para>
       </listitem>

       <listitem>
        <para>
         Non-immutable functions are not supported.
         <programlisting>
CREATE INCREMENTAL MATERIALIZED VIEW  mv_ivm12 AS SELECT i,j FROM mv_base_a WHERE i = random()::int;
ERROR:  functions in IMMV must be marked IMMUTABLE
         </programlisting>
        </para>
        </listitem>

       <listitem>
        <para>
         IMMVs do not support expressions that contains aggregates
        </para>
       </listitem>

       <listitem>
        <para>
         Logical replication does not support IMMVs.
        </para>
       </listitem>

      </itemizedlist>

     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>IF NOT EXISTS</literal></term>
    <listitem>
     <para>
      Do not throw an error if a materialized view with the same name already
      exists. A notice is issued in this case.  Note that there is no guarantee
      that the existing materialized view is anything like the one that would
      have been created.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable>table_name</replaceable></term>
    <listitem>
     <para>
      The name (optionally schema-qualified) of the materialized view to be
      created.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable>column_name</replaceable></term>
    <listitem>
     <para>
      The name of a column in the new materialized view.  If column names are
      not provided, they are taken from the output column names of the query.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>USING <replaceable class="parameter">method</replaceable></literal></term>
    <listitem>
     <para>
      This optional clause specifies the table access method to use to store
      the contents for the new materialized view; the method needs be an
      access method of type <literal>TABLE</literal>. See <xref
      linkend="tableam"/> for more information.  If this option is not
      specified, the default table access method is chosen for the new
      materialized view. See <xref linkend="guc-default-table-access-method"/>
      for more information.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
    <listitem>
     <para>
      This clause specifies optional storage parameters for the new
      materialized view; see
      <xref linkend="sql-createtable-storage-parameters"/> in the
      <xref linkend="sql-createtable"/> documentation for more
      information.  All parameters supported for <literal>CREATE
      TABLE</literal> are also supported for <literal>CREATE MATERIALIZED
      VIEW</literal>.
      See <xref linkend="sql-createtable"/> for more information.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>TABLESPACE <replaceable class="parameter">tablespace_name</replaceable></literal></term>
    <listitem>
     <para>
      The <replaceable class="parameter">tablespace_name</replaceable> is the name
      of the tablespace in which the new materialized view is to be created.
      If not specified, <xref linkend="guc-default-tablespace"/> is consulted.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable>query</replaceable></term>
    <listitem>
     <para>
      A <link linkend="sql-select"><command>SELECT</command></link>, <link linkend="sql-table"><command>TABLE</command></link>,
      or <link linkend="sql-values"><command>VALUES</command></link> command.  This query will run within a
      security-restricted operation; in particular, calls to functions that
      themselves create temporary tables will fail.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>WITH [ NO ] DATA</literal></term>
    <listitem>
     <para>
      This clause specifies whether or not the materialized view should be
      populated at creation time.  If not, the materialized view will be
      flagged as unscannable and cannot be queried until <command>REFRESH
      MATERIALIZED VIEW</command> is used.  Also, if the view is IMMV,
      triggers for maintaining the view are not created.
     </para>
    </listitem>
   </varlistentry>

  </variablelist>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   <command>CREATE MATERIALIZED VIEW</command> is a
   <productname>PostgreSQL</productname> extension.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-altermaterializedview"/></member>
   <member><xref linkend="sql-createtableas"/></member>
   <member><xref linkend="sql-createview"/></member>
   <member><xref linkend="sql-dropmaterializedview"/></member>
   <member><xref linkend="sql-refreshmaterializedview"/></member>
  </simplelist>
 </refsect1>

</refentry>