alter_table.sgml

postgresql8.3.4源码,开源数据库

       <para>
        A parent table to associate or de-associate with this table.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">new_owner</replaceable></term>
      <listitem>
       <para>
        The user name of the new owner of the table.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">new_tablespace</replaceable></term>
      <listitem>
       <para>
        The name of the tablespace to which the table will be moved.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">new_schema</replaceable></term>
      <listitem>
       <para>
        The name of the schema to which the table will be moved.
       </para>
      </listitem>
     </varlistentry>

    </variablelist>
 </refsect1>

 <refsect1>
  <title>Notes</title>

   <para>
    The key word <literal>COLUMN</literal> is noise and can be omitted.
   </para>

   <para>
    When a column is added with <literal>ADD COLUMN</literal>, all existing
    rows in the table are initialized with the column's default value
    (NULL if no <literal>DEFAULT</> clause is specified).
   </para>

   <para>
    Adding a column with a non-null default or changing the type of an
    existing column will require the entire table to be rewritten.  This
    might take a significant amount of time for a large table; and it will
    temporarily require double the disk space.
   </para>

   <para>
    Adding a <literal>CHECK</> or <literal>NOT NULL</> constraint requires
    scanning the table to verify that existing rows meet the constraint.
   </para>

   <para>
    The main reason for providing the option to specify multiple changes
    in a single <command>ALTER TABLE</> is that multiple table scans or
    rewrites can thereby be combined into a single pass over the table.
   </para>

   <para>
    The <literal>DROP COLUMN</literal> form does not physically remove
    the column, but simply makes it invisible to SQL operations.  Subsequent
    insert and update operations in the table will store a null value for the
    column. Thus, dropping a column is quick but it will not immediately
    reduce the on-disk size of your table, as the space occupied 
    by the dropped column is not reclaimed.  The space will be
    reclaimed over time as existing rows are updated.
   </para>

   <para>
    The fact that <literal>ALTER TYPE</> requires rewriting the whole table
    is sometimes an advantage, because the rewriting process eliminates
    any dead space in the table.  For example, to reclaim the space occupied
    by a dropped column immediately, the fastest way is:
<programlisting>
ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
</programlisting>
    where <literal>anycol</> is any remaining table column and
    <literal>anytype</> is the same type that column already has.
    This results in no semantically-visible change in the table,
    but the command forces rewriting, which gets rid of no-longer-useful
    data.
   </para>

   <para>
    The <literal>USING</literal> option of <literal>ALTER TYPE</> can actually
    specify any expression involving the old values of the row; that is, it
    can refer to other columns as well as the one being converted.  This allows
    very general conversions to be done with the <literal>ALTER TYPE</>
    syntax.  Because of this flexibility, the <literal>USING</literal>
    expression is not applied to the column's default value (if any); the
    result might not be a constant expression as required for a default.
    This means that when there is no implicit or assignment cast from old to
    new type, <literal>ALTER TYPE</> might fail to convert the default even
    though a <literal>USING</literal> clause is supplied.  In such cases,
    drop the default with <literal>DROP DEFAULT</>, perform the <literal>ALTER
    TYPE</>, and then use <literal>SET DEFAULT</> to add a suitable new
    default.  Similar considerations apply to indexes and constraints involving
    the column.
   </para>

   <para>
    If a table has any descendant tables, it is not permitted to add,
    rename, or change the type of a column in the parent table without doing
    the same to the descendants.  That is, <command>ALTER TABLE ONLY</command>
    will be rejected.  This ensures that the descendants always have
    columns matching the parent.
   </para>

   <para>
    A recursive <literal>DROP COLUMN</literal> operation will remove a
    descendant table's column only if the descendant does not inherit
    that column from any other parents and never had an independent
    definition of the column.  A nonrecursive <literal>DROP
    COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
    COLUMN</command>) never removes any descendant columns, but
    instead marks them as independently defined rather than inherited.
   </para>

   <para>
    The <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>,
    and <literal>TABLESPACE</> actions never recurse to descendant tables;
    that is, they always act as though <literal>ONLY</> were specified.
    Adding a constraint can recurse only for <literal>CHECK</> constraints.
   </para>

   <para>
    Changing any part of a system catalog table is not permitted.
   </para>

   <para>
    Refer to <xref linkend="sql-createtable"
    endterm="sql-createtable-title"> for a further description of valid
    parameters. <xref linkend="ddl"> has further information on
    inheritance.
   </para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   To add a column of type <type>varchar</type> to a table:
<programlisting>
ALTER TABLE distributors ADD COLUMN address varchar(30);
</programlisting>
  </para>

  <para>
   To drop a column from a table:
<programlisting>
ALTER TABLE distributors DROP COLUMN address RESTRICT;
</programlisting>
  </para>

  <para>
   To change the types of two existing columns in one operation:
<programlisting>
ALTER TABLE distributors
    ALTER COLUMN address TYPE varchar(80),
    ALTER COLUMN name TYPE varchar(100);
</programlisting>
  </para>

  <para>
   To change an integer column containing UNIX timestamps to <type>timestamp
   with time zone</type> via a <literal>USING</literal> clause:
<programlisting>
ALTER TABLE foo
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
</programlisting>
  </para>

  <para>
   The same, when the column has a default expression that won't automatically
   cast to the new data type:
<programlisting>
ALTER TABLE foo
    ALTER COLUMN foo_timestamp DROP DEFAULT,
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
    ALTER COLUMN foo_timestamp SET DEFAULT now();
</programlisting>
  </para>

  <para>
   To rename an existing column:
<programlisting>
ALTER TABLE distributors RENAME COLUMN address TO city;
</programlisting>
  </para>

  <para>
   To rename an existing table:
<programlisting>
ALTER TABLE distributors RENAME TO suppliers;
</programlisting>
  </para>

  <para>
   To add a not-null constraint to a column:
<programlisting>
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
</programlisting>
   To remove a not-null constraint from a column:
<programlisting>
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
</programlisting>
  </para>

  <para> 
   To add a check constraint to a table:
<programlisting>
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
</programlisting>
  </para>

  <para> 
   To remove a check constraint from a table and all its children:
<programlisting>
ALTER TABLE distributors DROP CONSTRAINT zipchk;
</programlisting>
  </para>

  <para> 
   To add a foreign key constraint to a table:
<programlisting>
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
</programlisting>
  </para>

  <para> 
   To add a (multicolumn) unique constraint to a table:
<programlisting>
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
</programlisting>
  </para>

  <para> 
   To add an automatically named primary key constraint to a table, noting
   that a table can only ever have one primary key:
<programlisting>
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
</programlisting>
  </para>

  <para> 
   To move a table to a different tablespace:
<programlisting>
ALTER TABLE distributors SET TABLESPACE fasttablespace;
</programlisting>
  </para>

  <para> 
   To move a table to a different schema:
<programlisting>
ALTER TABLE myschema.distributors SET SCHEMA yourschema;
</programlisting>
  </para>

 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   The <literal>ADD</literal>, <literal>DROP</>, and <literal>SET DEFAULT</>
   forms conform with the SQL standard.  The other forms are
   <productname>PostgreSQL</productname> extensions of the SQL standard.
   Also, the ability to specify more than one manipulation in a single
   <command>ALTER TABLE</> command is an extension.
  </para>

  <para>
   <command>ALTER TABLE DROP COLUMN</> can be used to drop the only
   column of a table, leaving a zero-column table.  This is an
   extension of SQL, which disallows zero-column tables.
  </para>
 </refsect1>
</refentry>