alter_table.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.40 2002/03/06 20:42:38 momjian Exp $
PostgreSQL documentation
-->

<refentry id="SQL-ALTERTABLE">
 <refmeta>
  <refentrytitle id="sql-altertable-title">
   ALTER TABLE
  </refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   ALTER TABLE
  </refname>
  <refpurpose>
   change the definition of a table
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1999-07-20</date>
  </refsynopsisdivinfo>
  <synopsis>
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
    ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
    ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable
    class="PARAMETER">value</replaceable> | DROP DEFAULT }
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
    ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
    ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE {PLAIN | EXTERNAL | EXTENDED | MAIN}
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
    RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable
    class="PARAMETER">newcolumn</replaceable>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
    RENAME TO <replaceable class="PARAMETER">new_table</replaceable>
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
    ADD <replaceable class="PARAMETER">table_constraint_definition</replaceable>
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> 
	DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> { RESTRICT | CASCADE }
ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
	OWNER TO <replaceable class="PARAMETER">new_owner</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"> new_table </replaceable></term>
      <listitem>
       <para>
	New name for the table.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER"> table_constraint_definition </replaceable></term>
      <listitem>
       <para>
	New table constraint for the 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>

    </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>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 <literal>ADD COLUMN</literal> form adds a new column to the table
   using the same syntax as <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
   The <literal>ALTER COLUMN SET/DROP DEFAULT</literal> forms
   allow you to set or remove the default for the column. Note that defaults
   only apply to subsequent <command>INSERT</command> commands; they do not
   cause rows already in the table to change.
   The <literal>ALTER COLUMN SET STATISTICS</literal> form allows you to
   set the statistics-gathering target for subsequent
   <xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
   The <literal>ALTER COLUMN SET STORAGE</literal> form allows the
   column storage mode to be set. This controls whether this column is
   held inline or in a supplementary table, and whether the data
   should be compressed or not. <literal>PLAIN</literal> must be used
   for fixed-length values such as <literal>INTEGER</literal> and is
   inline, uncompressed. <literal>MAIN</literal> is for inline,
   compressible data. <literal>EXTERNAL</literal> is for external,
   uncompressed data and <literal>EXTENDED</literal> is for external,
   compressed data. The use of <literal>EXTERNAL</literal> will make
   substring operations on a column faster, at the penalty of
   increased storage space.
   The <literal>RENAME</literal> clause causes the name of a table,
   column, index, sequence or view to change without changing any of the
   data. The data will remain of the same type and size after the
   command is executed.
   The ADD <replaceable class="PARAMETER">table_constraint_definition</replaceable> clause 
   adds a new constraint to the table using the same syntax as <xref
   linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">. 
   The DROP CONSTRAINT <replaceable class="PARAMETER">constraint</replaceable> clause 
   drops all constraints on the table (and its children) that match <replaceable class="PARAMETER">constraint</replaceable>.
   The OWNER clause changes the owner of the table, index, sequence or view to the
   user <replaceable class="PARAMETER">new user</replaceable>.
  </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>
    In the current implementation of <literal>ADD COLUMN</literal>,
    default and NOT NULL clauses for the new column are not supported.
    You can use the <literal>SET DEFAULT</literal> form
    of <command>ALTER TABLE</command> to set the default later.
    (You may also want to update the already existing rows to the
    new default value, using <xref linkend="sql-update" endterm="sql-update-title">.)
   </para>

   <para>
    In DROP CONSTRAINT, the RESTRICT keyword is required, although
    dependencies are not yet checked.  The CASCADE option is unsupported.  
    Currently DROP CONSTRAINT drops only CHECK constraints.
    To remove a PRIMARY or UNIQUE constraint, drop the 
    relevant index using the <xref linkend="SQL-DROPINDEX" endterm="sql-dropindex-title"> command.
    To remove FOREIGN KEY constraints you need to recreate
    and reload the table, using other parameters to the
    <xref linkend="SQL-CREATETABLE" endterm="sql-createtable-title"> command.
   </para>
   <para>
    For example, to drop all constraints on a table <literal>distributors</literal>:
    <programlisting>
CREATE TABLE temp AS SELECT * FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors AS SELECT * FROM temp;
DROP TABLE temp;
    </programlisting>
   </para>

   <para>
    You must own the table in order to change it.
    Changing any  part  of  the schema of a system
    catalog is not permitted.
    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 <type>varchar</type> 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>

  <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 RESTRICT;
   </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>
 </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>
     The <literal>ADD COLUMN</literal> form is compliant with the exception that
     it does not support defaults and NOT NULL constraints, as explained above.
     The <literal>ALTER COLUMN</literal> form is in full compliance.
    </para>

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

    <variablelist>
    <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.
       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>
   </para>

   <para>
    The clauses to rename tables, columns, indexes, and sequences are
    <productname>PostgreSQL</productname> extensions from SQL92.
   </para>
 
  </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:
-->