alter_table.sgml

关系型数据库 Postgresql 6.5.2

<refentry id="SQL-ALTERTABLE">
 <refmeta>
  <refentrytitle>
   ALTER TABLE
  </refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   ALTER TABLE
  </refname>
  <refpurpose>
   Modifies table properties
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1998-04-15</date>
  </refsynopsisdivinfo>
  <synopsis>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
    [ * ] ADD [ COLUMN ] <replaceable class="PARAMETER">ER">co</replaceable>BLE> <replaceable
    class="PARAMETER">type</replaceable>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
    [ * ] RENAME [ COLUMN ] <replaceable class="PARAMETER">ER">co</replaceable>BLE> TO <replaceable
    class="PARAMETER">newcolumn</replaceable>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
    RENAME TO <replaceable class="PARAMETER">newtable</replaceable>
  </synopsis>

  <refsect2 id="R2-SQL-ALTERTABLE-1">
   <refsect2info>
    <date>1998-04-15</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>
    <variablelist>
     <varlistentry>
      <term><replaceable class="PARAMETER"> table </replaceable></term>
      <listitem>
       <para>
	The name of an existing table to alter.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER"> column </replaceable></term>
      <listitem>
       <para>
	Name of a new or existing column.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER"> type </replaceable></term>
      <listitem>
       <para>
	Type of the new column.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER"> newcolumn </replaceable></term>
      <listitem>
       <para>
	New name for an existing column.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER"> newtable </replaceable></term>
      <listitem>
       <para>
	New name for an existing column.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

  <refsect2 id="R2-SQL-ALTERTABLE-2">
   <refsect2info>
    <date>1998-04-15</date>
   </refsect2info>
   <title>
    Outputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><computeroutput>
ALTER
       </computeroutput></term>
      <listitem>
       <para>
	Message returned from column or table renaming.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><computeroutput>
NEW
       </computeroutput></term>
      <listitem>
       <para>
	Message returned from column addition.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><computeroutput>
ERROR
       </computeroutput></term>
      <listitem>
       <para>
	Message returned if table or column is not available.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-SQL-ALTERTABLE-1">
  <refsect1info>
   <date>1998-04-15</date>
  </refsect1info>
  <title>
   Description
  </title>
  <para>
   <command>ALTER TABLE</command> changes the definition of an existing table.
   The new columns and their types are specified in the same style
   and with the the same restrictions as in <command>CREATE TABLE</command>.
   The RENAME clause causes the name of a table or column
   to change without changing any of the data contained in
   the affected table. Thus, the table or column will
   remain of the same type and size after this command is
   executed.
  </para>

  <para>
   You must own the table in order to change its schema.
  </para>

  <refsect2 id="R2-SQL-ALTERTABLE-3">
   <refsect2info>
    <date>1998-04-15</date>
   </refsect2info>
   <title>
    Notes
</title>
   <para>
    The keyword <literal>COLUMN</literal> is noise and can be omitted.
   </para>

   <para>
    <quote>[*]</quote> following a name of a table indicates that statement
    should be run over that table and all tables below it in the
    inheritance hierarchy.
    The <citetitle>PostgreSQL User's Guide</citetitle> has further
    information on inheritance.
   </para>

   <para>
    Refer to <command>CREATE TABLE</command> for a further description
    of valid arguments.
   </para>
  </refsect2>
 </refsect1>

 <refsect1 id="R1-SQL-ALTERTABLE-2">
  <title>
   Usage
  </title>
  <para>
   To add a column of type VARCHAR to a table:
   <programlisting>
ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
   </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>
 </refsect1>

 <refsect1 id="R1-SQL-ALTERTABLE-3">
  <title>
   Compatibility
  </title>

  <refsect2 id="R2-SQL-ALTERTABLE-4">
   <refsect2info>
    <date>1998-04-15</date>
   </refsect2info>
   <title>
    SQL92
   </title>
   <para>
    <command>ALTER TABLE/RENAME</command>
    is a <productname>Postgres</productname> language extension.
   </para>

   <para>
    SQL92 specifies some additional capabilities for <command>ALTER TABLE</command>
    statement which are not yet directly supported by
    <productname>Postgres</productname>:
   </para>

   <variablelist>
    <varlistentry>
     <term>
      <synopsis>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable> ALTER [
       COLUMN ] <replaceable class="PARAMETER">column</replaceable>
    SET DEFAULT <replaceable class="PARAMETER">default</replaceable>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable> ALTER [
       COLUMN ] <replaceable class="PARAMETER">column</replaceable>
    ADD [ CONSTRAINT <replaceable class="PARAMETER">>constrain</replaceable>> ] <replaceable
	class="PARAMETER">table-constraint</replaceable>
      </synopsis>
     </term>
     <listitem>
      <para>
       Puts the default value or constraint specified into the
       definition of column in the table.
       See <command>CREATE TABLE</command> for the
       syntax of the default and table-constraint clauses.
       If a default clause already exists, it will be replaced by
       the new definition. If any constraints on this column already
       exist, they will be retained using a boolean AND with the new
       constraint.
      </para>

      <para>
       Currently, to set new default constraints on an existing column
       the table must be recreated and reloaded:

       <programlisting>
CREATE TABLE temp AS SELECT * FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors (
    did      DECIMAL(3) DEFAULT 1,
    name     VARCHAR(40) NOT NULL,
    city     VARCHAR(30)
);
INSERT INTO distributors SELECT * FROM temp;
DROP TABLE temp;
       </programlisting>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>
      <synopsis>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
    DROP DEFAULT <replaceable class="PARAMETER">default</replaceable>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
    DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> { RESTRICT | CASCADE }
      </synopsis>
     </term>
     <listitem>
      <para>
       Removes the default value specified by default or the rule
       specified by constraint from the definition of a table. 
       If RESTRICT is specified only a constraint with no dependent
       constraints can be destroyed.
       If CASCADE is specified, Any constraints that are dependent on
       this constraint are also dropped.
      </para>

      <para>
       Currently, to remove a default value or constraints on an
       existing column the table must be recreated and reloaded:

       <programlisting>
CREATE TABLE temp AS SELECT * FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors AS SELECT * FROM temp;
DROP TABLE temp;
       </programlisting>
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>
      <synopsis>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
    DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { RESTRICT | CASCADE }
      </synopsis>
     </term>
     <listitem>
      <para>
       Removes a column from a table.
       If RESTRICT is specified only a column with no dependent
       objects can be destroyed.
       If CASCADE is specified, all objects that are dependent on
       this column are also dropped.
      </para>

      <para>
       Currently, to remove an existing column the table must be
       recreated and reloaded:

       <programlisting>
CREATE TABLE temp AS SELECT did, city FROM distributors;    
DROP TABLE distributors;
CREATE TABLE distributors (
    did      DECIMAL(3)  DEFAULT 1,
    name     VARCHAR(40) NOT NULL,
);
INSERT INTO distributors SELECT * FROM temp;
DROP TABLE temp;
       </programlisting>
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->