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

<refentry id="sql-createtableas">
 <indexterm zone="sql-createtableas">
  <primary>CREATE TABLE AS</primary>
 </indexterm>

 <refmeta>
  <refentrytitle>CREATE TABLE AS</refentrytitle>
  <manvolnum>7</manvolnum>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>CREATE TABLE AS</refname>
  <refpurpose>define a new table from the results of a query</refpurpose>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ 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>] [, ... ] ) | WITHOUT OIDS ]
    [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
    [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
    AS <replaceable>query</replaceable>
    [ WITH [ NO ] DATA ]
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>CREATE TABLE AS</command> creates a table and fills it
   with data computed by a <command>SELECT</command> command.
   The table columns have the
   names and data types associated with the output columns of the
   <command>SELECT</command> (except that you can override the column
   names by giving an explicit list of new column names).
  </para>

  <para>
   <command>CREATE TABLE AS</command> bears some resemblance to
   creating a view, but it is really quite different: it creates a new
   table and evaluates the query just once to fill the new table
   initially.  The new table will not track subsequent changes to the
   source tables of the query.  In contrast, a view re-evaluates its
   defining <command>SELECT</command> statement whenever it is
   queried.
  </para>

  <para>
   <command>CREATE TABLE AS</command> requires <literal>CREATE</literal>
   privilege on the schema used for the table.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><literal>GLOBAL</literal> or <literal>LOCAL</literal></term>
    <listitem>
     <para>
      Ignored for compatibility.  Use of these keywords is deprecated;
      refer to <xref linkend="sql-createtable"/> for details.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>

  <variablelist>
   <varlistentry>
    <term><literal>TEMPORARY</literal> or <literal>TEMP</literal></term>
    <listitem>
     <para>
      If specified, the table is created as a temporary table.
      Refer to <xref linkend="sql-createtable"/> for details.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>UNLOGGED</literal></term>
    <listitem>
     <para>
      If specified, the table is created as an unlogged table.
      Refer to <xref linkend="sql-createtable"/> for details.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>IF NOT EXISTS</literal></term>
    <listitem>
     <para>
      Do not throw an error if a relation with the same name already
      exists; simply issue a notice and leave the table unmodified.
     </para>
    </listitem>
   </varlistentry>

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

   <varlistentry>
    <term><replaceable>column_name</replaceable></term>
    <listitem>
     <para>
      The name of a column in the new table.  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>