Home • Explore • Blog • CI
☰



postgresql
14th chunk of `doc/src/sgml/ref/alter_table.sgml`
7569701cefc2bcc12e5f65da74f6fe566f91c78b43b6af5b0000000100000fa0
 </varlistentry>

   <varlistentry id="sql-altertable-desc-cluster-on">
    <term><literal>CLUSTER ON</literal></term>
    <listitem>
     <para>
      This form selects the default index for future
      <link linkend="sql-cluster"><command>CLUSTER</command></link>
      operations.  It does not actually re-cluster the table.
     </para>
     <para>
      Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-altertable-desc-set-without-cluster">
    <term><literal>SET WITHOUT CLUSTER</literal></term>
    <listitem>
     <para>
      This form removes the most recently used
      <link linkend="sql-cluster"><command>CLUSTER</command></link>
      index specification from the table.  This affects
      future cluster operations that don't specify an index.
     </para>
     <para>
      Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-altertable-desc-set-without-oids">
    <term><literal>SET WITHOUT OIDS</literal></term>
    <listitem>
     <para>
      Backward-compatible syntax for removing the <literal>oid</literal>
      system column.  As <literal>oid</literal> system columns cannot be
      added anymore, this never has an effect.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-altertable-desc-set-access-method">
    <term><literal>SET ACCESS METHOD</literal></term>
    <listitem>
     <para>
      This form changes the access method of the table by rewriting it
      using the indicated access method; specifying
      <literal>DEFAULT</literal> selects the access method set as the
      <xref linkend="guc-default-table-access-method"/> configuration
      parameter.
      See <xref linkend="tableam"/> for more information.
     </para>
     <para>
      When applied to a partitioned table, there is no data to rewrite,
      but partitions created afterwards will default to the given access
      method unless overridden by a <literal>USING</literal> clause.
      Specifying <varname>DEFAULT</varname> removes a previous value,
      causing future partitions to default to
      <varname>default_table_access_method</varname>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-altertable-desc-set-tablespace">
    <term><literal>SET TABLESPACE</literal></term>
    <listitem>
     <para>
      This form changes the table's tablespace to the specified tablespace and
      moves the data file(s) associated with the table to the new tablespace.
      Indexes on the table, if any, are not moved; but they can be moved
      separately with additional <literal>SET TABLESPACE</literal> commands.
      When applied to a partitioned table, nothing is moved, but any
      partitions created afterwards with
      <command>CREATE TABLE PARTITION OF</command> will use that tablespace,
      unless overridden by a <literal>TABLESPACE</literal> clause.
     </para>

     <para>
      All tables in the current database in a tablespace can be moved by using
      the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables
      to be moved first and then move each one.  This form also supports
      <literal>OWNED BY</literal>, which will only move tables owned by the
      roles specified.  If the <literal>NOWAIT</literal> option is specified
      then the command will fail if it is unable to acquire all of the locks
      required immediately.  Note that system catalogs are not moved by this
      command; use <command>ALTER DATABASE</command> or explicit
      <command>ALTER TABLE</command> invocations instead if desired.  The
      <literal>information_schema</literal> relations are not considered part
      of the system catalogs and will be moved.
      See also
      <link linkend="sql-createtablespace"><command>CREATE TABLESPACE</command></link>.
     </para>
Title: ALTER TABLE: Clustering, OIDs, Access Methods, and Tablespaces
Summary
This section of the ALTER TABLE documentation covers options for managing table clustering, including setting or removing the default cluster index. It also discusses the removal of OIDs (which is now a no-op), setting the table access method (including setting the default access method for future partitions), and moving a table to a different tablespace (with options to move all tables in a tablespace or those owned by specific roles). The NOWAIT option is mentioned for failing immediately if required locks cannot be acquired.