ALTER_TABLE(l)                                     ALTER_TABLE(l)

NAME
       ALTER TABLE - Modifies table properties

SYNOPSIS
       ALTER TABLE table
           [ * ] ADD [ COLUMN ] ER">coBLE> type
       ALTER TABLE table
           [ * ] RENAME [ COLUMN ] ER">coBLE> TO newcolumn
       ALTER TABLE table
           RENAME TO newtable

   INPUTS
       table  The name of an existing table to alter.

       column Name of a new or existing column.

       type   Type of the new column.

       newcolumn
              New name for an existing column.

       newtable
              New name for an existing column.

   OUTPUTS
       ALTER  Message returned from column or table renaming.

       NEW    Message returned from column addition.

       ERROR  Message  returned  if table or column is not avail-
              able.

DESCRIPTION
       ALTER TABLE 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  CREATE
       TABLE.   The  RENAME  clause causes the name of a table or
       column to change without changing any  of  the  data  con-
       tained  in  the  affected table. Thus, the table or column
       will remain of the same type and size after  this  command
       is executed.

       You must own the table in order to change its schema.

   NOTES
       The keyword COLUMN is noise and can be omitted.

       ``*''  following  a  name  of  a  table indicates that the
       statement should be run over that  table  and  all  tables
       below  it  in  the  inheritance hierarchy; by default, the
       attribute will not be added to or renamed in  any  of  the
       subclasses.   This  should  always  be done when adding or
       modifying an attribute in a  superclass.  If  it  is  not,
       queries on the inheritance hierarchy such as

       SELECT NewColumn FROM SuperClass*

       will  not  work  because the subclasses will be missing an
       attribute found in the superclass.

       For  efficiency  reasons,   default   values   for   added
       attributes  are  not  placed  in  existing  instances of a
       class.  That is, existing instances will have NULL  values
       in  the  new attributes. If non-NULL values are desired, a
       subsequent UPDATE query (update(l)) should be run.

       You must own the class in  order  to  change  its  schema.
       Renaming any part of the schema of a system catalog is not
       permitted.  The PostgreSQL User's Guide has further infor-
       mation on inheritance.

       Refer  to  CREATE TABLE for a further description of valid
       arguments.

USAGE
       To add a column of type VARCHAR to a table:

       ALTER TABLE distributors ADD COLUMN address VARCHAR(30);

       To rename an existing column:

       ALTER TABLE distributors RENAME COLUMN address TO city;

       To rename an existing table:

       ALTER TABLE distributors RENAME TO suppliers;

COMPATIBILITY
   SQL92
       ALTER TABLE/RENAME is a Postgres language extension.

       SQL92 specifies some  additional  capabilities  for  ALTER
       TABLE  statement  which  are not yet directly supported by
       Postgres:

       Puts the default value or constraint specified into the
              definition of column  in  the  table.   See  CREATE
              TABLE  for the syntax of the default and table-con-
              straint  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.

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

              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;

       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.

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

              CREATE TABLE temp AS SELECT * FROM distributors;
              DROP TABLE distributors;
              CREATE TABLE distributors AS SELECT * FROM temp;
              DROP TABLE temp;

       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.

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

              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;

SQL - Language Statements 15 August 1999                        1