Home • Explore • Blog • CI
☰



postgresql
5th chunk of `doc/src/sgml/ref/create_table.sgml`
6253c02d1508ec3ae6c0a6e9de40204faa0c9abcaa1a58c90000000100000fa0
 table exists, unless they are referenced
      with schema-qualified names. Any indexes created on a temporary
      table are automatically temporary as well.
     </para>

     <para>
      The <link linkend="autovacuum">autovacuum daemon</link> cannot
      access and therefore cannot vacuum or analyze temporary tables.
      For this reason, appropriate vacuum and analyze operations should be
      performed via session SQL commands.  For example, if a temporary
      table is going to be used in complex queries, it is wise to run
      <command>ANALYZE</command> on the temporary table after it is populated.
     </para>

     <para>
      Optionally, <literal>GLOBAL</literal> or <literal>LOCAL</literal>
      can be written before <literal>TEMPORARY</literal> or <literal>TEMP</literal>.
      This presently makes no difference in <productname>PostgreSQL</productname>
      and is deprecated; see
      <xref linkend="sql-createtable-compatibility"/> below.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-unlogged">
    <term><literal>UNLOGGED</literal></term>
    <listitem>
     <para>
      If specified, the table is created as an unlogged table.  Data written
      to unlogged tables is not written to the write-ahead log (see <xref
      linkend="wal"/>), which makes them considerably faster than ordinary
      tables.  However, they are not crash-safe: an unlogged table is
      automatically truncated after a crash or unclean shutdown.  The contents
      of an unlogged table are also not replicated to standby servers.
      Any indexes created on an unlogged table are automatically unlogged as
      well.
     </para>

     <para>
      If this is specified, any sequences created together with the unlogged
      table (for identity or serial columns) are also created as unlogged.
     </para>

     <para>
      This form is not supported for partitioned tables.
     </para>
    </listitem>
   </varlistentry>

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

   <varlistentry id="sql-createtable-parms-table-name">
    <term><replaceable class="parameter">table_name</replaceable></term>
    <listitem>
     <para>
      The name (optionally schema-qualified) of the table to be created.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-type-name">
    <term><literal>OF <replaceable class="parameter">type_name</replaceable></literal></term>
    <listitem>
     <para>
      Creates a <firstterm>typed table</firstterm>, which takes its
      structure from the specified stand-alone composite type (that is,
      one created using <xref linkend="sql-createtype"/>) though it still
      produces a new composite type as well.  The table will have a
      dependency on the referenced type, meaning that cascaded alter and
      drop actions on that type will propagate to the table.
     </para>

     <para>
      A typed table always has the same column names and data types as the
      type it is derived from, so you cannot specify additional columns.
      But the <literal>CREATE TABLE</literal> command can add defaults
      and constraints to the table, as well as specify storage parameters.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-column-name">
    <term><replaceable class="parameter">column_name</replaceable></term>
    <listitem>
     <para>
      The name of a column to be created in the new table.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-createtable-parms-data-type">
    <term><replaceable
Title: CREATE TABLE Parameters: UNLOGGED, IF NOT EXISTS, Table Name, and Typed Tables
Summary
This section details additional parameters for the CREATE TABLE command. It explains the UNLOGGED option, which creates tables that aren't written to the write-ahead log, offering speed but sacrificing crash safety and replication. It also describes the IF NOT EXISTS option, which prevents errors if a table with the same name exists, issuing a notice instead. It covers the table_name parameter, allowing schema-qualified names, and the OF type_name clause for creating typed tables based on a stand-alone composite type, inheriting column names and data types but allowing additional defaults and constraints.