ALTER TABLE - Modifies table properties


SYNOPSIS

       ALTER TABLE [ ONLY ] table [ * ]
           ADD [ COLUMN ] column type
       ALTER TABLE [ ONLY ] table [ * ]
           ALTER [ COLUMN ] column { SET DEFAULT value | DROP DEFAULT }
       ALTER TABLE table [ * ]
           RENAME [ COLUMN ] column TO newcolumn
       ALTER TABLE table
           RENAME TO newtable
       ALTER TABLE table
           ADD table constraint definition
       ALTER TABLE table
            OWNER TO new owner

   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 the table.

       table constraint definition
              New table constraint for the table

       New user
              The user name of the new owner of the table.

   OUTPUTS
       ALTER  Message returned from column or table renaming.

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


DESCRIPTION

       ALTER TABLE changes the definition of an  existing  table.
       The  ADD  COLUMN form adds a new column to the table using
       the same syntax as  CREATE  TABLE  [create_table(l)].  The
       ALTER  COLUMN form allows you to set or remove the default
       for the column. Note that defaults  only  apply  to  newly
       inserted  rows.   The  RENAME  clause causes the name of a
       table or column to change without changing any of the data

       will remain of the same type and size after  this  command
       is  executed.   The ADD table constraint definition clause
       adds a new constraint to the table using the  same  syntax
       as  CREATE  TABLE  [create_table(l)].   The  OWNER  clause
       chnages the owner of the table to the user  new user.

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

   NOTES
       The keyword COLUMN is noise and can be omitted.

       In the  current  implementation,  default  and  constraint
       clauses  for  the  new column will be ignored. You can use
       the SET DEFAULT form of ALTER TABLE  to  set  the  default
       later.  (You will also have to update the already existing
       rows to the new default value, using UPDATE  [update(l)].)

       In  the  current  implementation,  only  FOREIGN  KEY con­
       straints can be added to a table. To create  or  remove  a
       unique constraint, create a unique index (see CREATE INDEX
       [create_index(l)]). To add check constraints you  need  to
       recreate  and  reload the table, using other parameters to
       the CREATE TABLE [create_table(l)] command.

       You must own the table in order to  change  it.   Renaming
       any  part of the schema of a system catalog is not permit­
       ted.  The PostgreSQL User's Guide has further  information
       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;

       To add a foreign key constraint to a table:



COMPATIBILITY

   SQL92
       The ADD COLUMN form is compliant with the  exception  that
       it does not support defaults and constraints, as explained
       above.  The ALTER COLUMN form is in full compliance.

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

       Removes a table constraint (such as a check constraint,
              unique constraint, or foreign key  constraint).  To
              remove  a  unique  constraint, drop a unique index.
              To remove other kinds of constraints  you  need  to
              recreate  and reload the table, using other parame­
              ters to the CREATE TABLE [create_table(l)] command.

              For  example,  to  drop  any constraints on a table
              distributors:

              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.
              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;

       The  clauses  to  rename  columns  and tables are Postgres
       extensions from SQL92.


Man(1) output converted with man2html