Home • Explore • Blog • CI
☰



postgresql
20th chunk of `doc/src/sgml/ref/alter_table.sgml`
a3e5cd3f2eea0e7fde9b83f87027ae09ca9d4d96f0eef5f70000000100000fb4
 <literal>CONCURRENTLY</literal> is specified, it runs using a reduced
      lock level to avoid blocking other sessions that might be accessing the
      partitioned table.  In this mode, two transactions are used internally.
      During the first transaction, a <literal>SHARE UPDATE EXCLUSIVE</literal>
      lock is taken on both parent table and partition, and the partition is
      marked as undergoing detach; at that point, the transaction is committed
      and all other transactions using the partitioned table are waited for.
      Once all those transactions have completed, the second transaction
      acquires <literal>SHARE UPDATE EXCLUSIVE</literal> on the partitioned
      table and <literal>ACCESS EXCLUSIVE</literal> on the partition,
      and the detach process completes.  A <literal>CHECK</literal> constraint
      that duplicates the partition constraint is added to the partition.
      <literal>CONCURRENTLY</literal> cannot be run in a transaction block and
      is not allowed if the partitioned table contains a default partition.
     </para>
     <para>
      If <literal>FINALIZE</literal> is specified, a previous
      <literal>DETACH CONCURRENTLY</literal> invocation that was canceled or
      interrupted is completed.
      At most one partition in a partitioned table can be pending detach at
      a time.
     </para>
    </listitem>
   </varlistentry>

  </variablelist>
  </para>

  <para>
   All the forms of <command>ALTER TABLE</command> that act on a single table,
   except <literal>RENAME</literal>, <literal>SET SCHEMA</literal>,
   <literal>ATTACH PARTITION</literal>, and
   <literal>DETACH PARTITION</literal> can be combined into
   a list of multiple alterations to be applied together.  For example, it
   is possible to add several columns and/or alter the type of several
   columns in a single command.  This is particularly useful with large
   tables, since only one pass over the table need be made.
  </para>

  <para>
   You must own the table to use <command>ALTER TABLE</command>.
   To change the schema or tablespace of a table, you must also have
   <literal>CREATE</literal> privilege on the new schema or tablespace.
   To add the table as a new child of a parent table, you must own the parent
   table as well.  Also, to attach a table as a new partition of the table,
   you must own the table being attached.
   To alter the owner, you must be able to <literal>SET ROLE</literal> to the
   new owning role, and that role must have <literal>CREATE</literal>
   privilege on the table's schema.
   (These restrictions enforce that altering the owner
   doesn't do anything you couldn't do by dropping and recreating the table.
   However, a superuser can alter ownership of any table anyway.)
   To add a column or alter a column type or use the <literal>OF</literal>
   clause, you must also have <literal>USAGE</literal> privilege on the data
   type.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

    <variablelist>

     <varlistentry id="sql-altertable-parms-if-exists">
      <term><literal>IF EXISTS</literal></term>
      <listitem>
       <para>
        Do not throw an error if the table does not exist. A notice is issued
        in this case.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="sql-altertable-parms-name">
      <term><replaceable class="parameter">name</replaceable></term>
      <listitem>
       <para>
        The name (optionally schema-qualified) of an existing table to
        alter. If <literal>ONLY</literal> is specified before the table name, only
        that table is altered. If <literal>ONLY</literal> is not specified, the table
        and all its descendant tables (if any) are altered.  Optionally,
        <literal>*</literal> can be specified after the table name to explicitly
        indicate that descendant tables are included.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="sql-altertable-parms-column-name">
Title: ALTER TABLE: Detaching Partitions (Continued) and General Usage
Summary
This section details the FINALIZE option for DETACH CONCURRENTLY, allowing completion of previously interrupted detach operations. It also notes the limitation of only one partition being pending detach at a time. It summarizes the forms of ALTER TABLE that can be combined into a list of multiple alterations, excluding RENAME, SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION. It then specifies the required privileges for using ALTER TABLE, including ownership, CREATE privilege on the new schema or tablespace, ownership of the parent table for adding a child, and the ability to SET ROLE to the new owning role. The section concludes with definitions of the IF EXISTS and name parameters.