Provided by: postgresql-client-8.0_8.0.7-2build1_i386 bug


       ALTER TABLE - change the definition of a table


       ALTER TABLE [ ONLY ] name [ * ]
           action [, ... ]
       ALTER TABLE [ ONLY ] name [ * ]
           RENAME [ COLUMN ] column TO new_column
       ALTER TABLE name
           RENAME TO new_name

       where action is one of:

           ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
           DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
           ALTER [ COLUMN ] column TYPE type [ USING expression ]
           ALTER [ COLUMN ] column SET DEFAULT expression
           ALTER [ COLUMN ] column DROP DEFAULT
           ALTER [ COLUMN ] column { SET | DROP } NOT NULL
           ALTER [ COLUMN ] column SET STATISTICS integer
           ADD table_constraint
           DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
           CLUSTER ON index_name
           OWNER TO new_owner
           SET TABLESPACE tablespace_name


       ALTER  TABLE  changes  the  definition of an existing table.  There are
       several subforms:

              This form adds a new column to the table using the  same  syntax
              as CREATE TABLE [create_table(7)].

              This  form  drops  a  column  from  a  table.  Indexes and table
              constraints involving the column will be  automatically  dropped
              as  well.  You  will need to say CASCADE if anything outside the
              table depends on the column, for example, foreign key references
              or views.

              This  form  changes the type of a column of a table. Indexes and
              simple  table  constraints  involving   the   column   will   be
              automatically  converted to use the new column type by reparsing
              the originally supplied expression. The  optional  USING  clause
              specifies  how  to compute the new column value from the old; if
              omitted, the default conversion is the  same  as  an  assignment
              cast  from old data type to new. A USING clause must be provided
              if there is no implicit or assignment cast from old to new type.

              These  forms  set or remove the default value for a column.  The
              default values only apply to subsequent INSERT commands; they do
              not  cause  rows  already  in the table to change.  Defaults may
              also be created for views, in which case they are inserted  into
              INSERT  statements  on the view before the view’s ON INSERT rule
              is applied.

              These forms change whether a column  is  marked  to  allow  null
              values  or  to reject null values. You can only use SET NOT NULL
              when the column contains no null values.

              This form sets the per-column  statistics-gathering  target  for
              subsequent  ANALYZE  [analyze(7)] operations.  The target can be
              set in the range 0 to 1000;  alternatively,  set  it  to  -1  to
              revert   to   using   the   system   default  statistics  target
              (default_statistics_target).  For more information on the use of
              statistics  by  the  PostgreSQL  query  planner,  refer  to  the

              This form sets the storage mode  for  a  column.  This  controls
              whether  this column is held inline or in a supplementary table,
              and whether the data should be compressed or not. PLAIN must  be
              used  for  fixed-length  values  such  as integer and is inline,
              uncompressed. MAIN is for inline, compressible data. EXTERNAL is
              for  external,  uncompressed data, and EXTENDED is for external,
              compressed data. EXTENDED is the default  for  most  data  types
              that  support  non-PLAIN  storage.   Use  of  EXTERNAL will make
              substring operations on text and bytea columns  faster,  at  the
              penalty  of  increased  storage  space.  Note  that  SET STORAGE
              doesn’t itself change anything in the table, it  just  sets  the
              strategy  to  be  pursued  during future table updates.  See the
              documentation for more information.

       ADD table_constraint
              This form adds a new constraint to a table using the same syntax
              as CREATE TABLE [create_table(7)].

              This  form drops constraints on a table.  Currently, constraints
              on tables are not required to have unique names, so there may be
              more  than  one  constraint  matching  the  specified  name. All
              matching constraints will be dropped.

              This  form  selects  the  default  index  for   future   CLUSTER
              [cluster(7)]  operations.  It  does  not actually re-cluster the

              This form removes the most recently  used  CLUSTER  [cluster(7)]
              index  specification from the table. This affects future cluster
              operations that don’t specify an index.

              This form removes the oid system column from the table. This  is
              exactly  equivalent  to DROP COLUMN oid RESTRICT, except that it
              will not complain if there is already no oid column.

              Note that there is no variant of ALTER TABLE that allows OIDs to
              be restored to a table once they have been removed.

       OWNER  This  form  changes  the owner of the table, index, sequence, or
              view to the specified user.

              This form  changes  the  table’s  tablespace  to  the  specified
              tablespace  and moves the data file(s) associated with the table
              to the new tablespace.  Indexes on the table, if  any,  are  not
              moved;  but  they  can  be  moved separately with additional SET
              TABLESPACE    commands.     See    also    CREATE     TABLESPACE

       RENAME The  RENAME  forms  change  the  name  of  a table (or an index,
              sequence, or view) or the name of  an  individual  column  in  a
              table. There is no effect on the stored data.

       All  the  actions except RENAME can be combined into a list of multiple
       alterations to apply in parallel. For example, it is  possible  to  add
       several  columns  and/or  alter the type of several columns in a single
       command. This is particularly useful with large tables, since only  one
       pass over the table need be made.

       You  must  own  the  table  to  use ALTER TABLE; except for ALTER TABLE
       OWNER, which may only be executed by a superuser.


       name   The name (possibly schema-qualified) of  an  existing  table  to
              alter. If ONLY is specified, only that table is altered. If ONLY
              is not specified, the table and all its  descendant  tables  (if
              any)  are  updated.  *  can  be  appended  to  the table name to
              indicate that descendant tables are to be altered,  but  in  the
              current  version,  this  is  the  default behavior. (In releases
              before 7.1, ONLY was the default behavior. The  default  can  be
              altered     by     changing    the    configuration    parameter

       column Name of a new or existing column.

              New name for an existing column.

              New name for the table.

       type   Data type of the new column, or new data type  for  an  existing

              New table constraint for the table.

              Name of an existing constraint to drop.

              Automatically  drop objects that depend on the dropped column or
              constraint (for example, views referencing the column).

              Refuse to drop  the  column  or  constraint  if  there  are  any
              dependent objects. This is the default behavior.

              The  index  name  on  which  the  table  should  be  marked  for

              The user name of the new owner of the table.

              The tablespace name to which the table will be moved.


       The key word COLUMN is noise and can be omitted.

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

       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 may
       take a significant amount of time  for  a  large  table;  and  it  will
       temporarily require double the disk space.

       Adding  a  CHECK  or NOT NULL constraint requires scanning the table to
       verify that existing rows meet the constraint.

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

       The DROP COLUMN 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.

       The fact  that  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

       ALTER TABLE table ALTER COLUMN anycol TYPE anytype;

       where anycol is any remaining table column and 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.

       The USING option of 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 ALTER TYPE syntax. Because of this
       flexibility, the USING  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, ALTER TYPE may fail to convert
       the default even though a USING clause is supplied. In such cases, drop
       the default with DROP DEFAULT, perform the ALTER TYPE, and then use SET
       DEFAULT to add a suitable new default. Similar considerations apply  to
       indexes and constraints involving the column.

       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, ALTER TABLE ONLY will be
       rejected.  This  ensures  that  the  descendants  always  have  columns
       matching the parent.

       A  recursive  DROP  COLUMN  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 DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never
       removes any descendant columns, but instead marks them as independently
       defined rather than inherited.

       Changing any part of a system catalog table is not permitted.

       Refer to CREATE TABLE [create_table(7)] for a  further  description  of
       valid   parameters.   the  documentation  has  further  information  on


       To add a column of type varchar to a table:

       ALTER TABLE distributors ADD COLUMN address varchar(30);

       To drop a column from a table:

       ALTER TABLE distributors DROP COLUMN address RESTRICT;

       To change the types of two existing columns in one operation:

       ALTER TABLE distributors
           ALTER COLUMN address TYPE varchar(80),
           ALTER COLUMN name TYPE varchar(100);

       To change an integer column containing  UNIX  timestamps  to  timestamp
       with time zone via a USING clause:

       ALTER TABLE foo
           ALTER COLUMN foo_timestamp TYPE timestamp with time zone
               timestamp with time zone ’epoch’ + foo_timestamp * interval ’1 second’;

       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 not-null constraint to a column:

       ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

       To remove a not-null constraint from a column:

       ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

       To add a check constraint to a table:

       ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

       To remove a check constraint from a table and all its children:

       ALTER TABLE distributors DROP CONSTRAINT zipchk;

       To add a foreign key constraint to a table:

       ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;

       To add a (multicolumn) unique constraint to a table:

       ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

       To add an automatically named primary key constraint to a table, noting
       that a table can only ever have one primary key:

       ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

       To move a table to a different tablespace:

       ALTER TABLE distributors SET TABLESPACE fasttablespace;


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

       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.