Provided by: postgresql-client-8.2_8.2.7-1_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
       ALTER TABLE name
           SET SCHEMA new_schema

       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 ]
           DISABLE TRIGGER [ trigger_name | ALL | USER ]
           ENABLE TRIGGER [ trigger_name | ALL | USER ]
           CLUSTER ON index_name
           SET ( storage_parameter = value [, ... ] )
           RESET ( storage_parameter [, ... ] )
           INHERIT parent_table
           NO INHERIT parent_table
           OWNER TO new_owner
           SET TABLESPACE new_tablespace


       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 in 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 in 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 the specified constraint on a table.

              These forms disable or enable trigger(s) belonging to the table.
              A disabled trigger is still known to  the  system,  but  is  not
              executed  when  its  triggering  event  occurs.  For  a deferred
              trigger, the enable status is checked when the event occurs, not
              when  the trigger function is actually executed. One may disable
              or enable a single trigger specified by name, or all triggers on
              the  table, or only user triggers (this option excludes triggers
              that are used to implement foreign key  constraints).  Disabling
              or  enabling  constraint triggers requires superuser privileges;
              it should be done with caution since of course the integrity  of
              the  constraint  cannot  be  guaranteed  if the triggers are not

              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.

       SET ( storage_parameter = value [, ... ] )
              This  form changes one or more storage parameters for the table.
              See CREATE TABLE [create_table(7)] for details on the  available
              parameters.  Note  that  the table contents will not be modified
              immediately by this command; depending on the parameter you  may
              need  to rewrite the table to get the desired effects.  That can
              be done with CLUSTER [cluster(7)] or one of the forms  of  ALTER
              TABLE that forces a table rewrite.

              Note: While CREATE TABLE allows OIDS to be specified in the WITH
              (storage_parameter) syntax, ALTER TABLE does not treat OIDS as a
              storage parameter.

       RESET ( storage_parameter [, ... ] )
              This  form  resets  one  or  more  storage  parameters  to their
              defaults. As with SET, a table rewrite may be needed  to  update
              the table entirely.

       INHERIT parent_table
              This  form adds the target table as a new child of the specified
              parent table. Subsequently,  queries  against  the  parent  will
              include records of the target table. To be added as a child, the
              target table must already contain all the same  columns  as  the
              parent (it could have additional columns, too). The columns must
              have matching data types, and if they have NOT NULL  constraints
              in  the  parent then they must also have NOT NULL constraints in
              the child.

              There must also be  matching  child-table  constraints  for  all
              CHECK  constraints of the parent. Currently UNIQUE, PRIMARY KEY,
              and FOREIGN KEY constraints are not  considered,  but  this  may
              change in the future.

       NO INHERIT parent_table
              This  form removes the target table from the list of children of
              the specified parent table.  Queries against  the  parent  table
              will no longer include records drawn from the target table.

       OWNER  This  form  changes the owner of the table, 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.

              This  form  moves  the  table  into  another  schema. Associated
              indexes, constraints, and sequences owned by table  columns  are
              moved as well.

       All  the  actions  except  RENAME and SET SCHEMA 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.  To change the schema of a
       table, you must also have CREATE privilege on the new schema.   To  add
       the  table  as  a  new child of a parent table, you must own the parent
       table as well.  To alter the owner,  you  must  also  be  a  direct  or
       indirect  member of the new owning role, and that role must have CREATE
       privilege on the  table’s  schema.  (These  restrictions  enforce  that
       altering  the owner doesn’t do anything you couldn’t do by dropping and
       recreating the table.  However, a superuser can alter ownership of  any
       table anyway.)


       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.

              Name of a single trigger to disable or enable.

       ALL    Disable or enable all triggers belonging to  the  table.   (This
              requires  superuser  privilege  if  any  of the triggers are for
              foreign key constraints.)

       USER   Disable or enable all triggers belonging to the table except for
              foreign key constraint triggers.

              The  index  name  on  which  the  table  should  be  marked  for

              The name of a table storage parameter.

       value  The new value for a table storage parameter.  This  might  be  a
              number or a word depending on the parameter.

              A parent table to associate or de-associate with this table.

              The user name of the new owner of the table.

              The name of the tablespace to which the table will be moved.

              The name of the schema 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.

       The  TRIGGER,  CLUSTER,  OWNER, and TABLESPACE actions never recurse to
       descendant tables; that  is,  they  always  act  as  though  ONLY  were
       specified.  Adding a constraint can recurse only for CHECK constraints.

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

       The  same,  when  the  column  has  a  default  expression  that  won’t
       automatically cast to the new data type:

       ALTER TABLE foo
           ALTER COLUMN foo_timestamp DROP DEFAULT,
           ALTER COLUMN foo_timestamp TYPE timestamp with time zone
               timestamp with time zone ’epoch’ + foo_timestamp * interval ’1 second’,
           ALTER COLUMN foo_timestamp SET DEFAULT now();

       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;

       To move a table to a different schema:

       ALTER TABLE myschema.distributors SET SCHEMA yourschema;


       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.