Provided by: postgresql-client-8.4_8.4.11-1_amd64 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 [ SET DATA ] 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 ]
           ENABLE REPLICA TRIGGER trigger_name
           ENABLE ALWAYS TRIGGER trigger_name
           DISABLE RULE rewrite_rule_name
           ENABLE RULE rewrite_rule_name
           ENABLE REPLICA RULE rewrite_rule_name
           ENABLE ALWAYS RULE rewrite_rule_name
           CLUSTER ON index_name
           SET WITH OIDS
           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

              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  can  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  10000;
              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 documentation.

              This  form sets the storage mode for a column. This controls whether this column is
              held inline or in  a  secondary  TOAST  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 very large text and bytea values run
              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

              This form drops the specified constraint on a table.

              These  forms configure the firing of 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 can  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 executed.  The trigger
              firing   mechanism   is   also   affected    by    the    configuration    variable
              session_replication_role.  Simply  enabled  triggers will fire when the replication
              role is ``origin'' (the  default)  or  ``local''.  Triggers  configured  as  ENABLE
              REPLICA  will  only  fire  if  the  session  is  in  ``replica'' mode, and triggers
              configured as ENABLE ALWAYS will fire regardless of the current replication mode.

              These forms configure the firing of  rewrite  rules  belonging  to  the  table.   A
              disabled  rule  is  still  known  to  the  system,  but is not applied during query
              rewriting. The semantics are as for disabled/enabled triggers.  This  configuration
              is  ignored  for  ON  SELECT rules, which are always applied in order to keep views
              working even if the current session is in a non-default replication role.

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

              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 adds an oid system column to the table (see in  the  documentation).   It
              does nothing if the table already has OIDs.

              Note  that  this  is  not equivalent to ADD COLUMN oid oid; that would add a normal
              column that happened to be named oid, not a system column.

              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.

       SET ( storage_parameter = value [, ... ] )
              This form changes one or  more  storage  parameters  for  the  table.  See  Storage
              Parameters [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 might 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.
              Instead use the SET WITH OIDS and SET WITHOUT OIDS forms to change OID status.

       RESET ( storage_parameter [, ... ] )
              This  form  resets one or more storage parameters to their defaults. As with SET, a
              table rewrite might 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 might 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 [create_tablespace(7)].

       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  any
              descendant tables are altered.

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

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

              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 might take a significant amount of time for
       a large table; and it will temporarily require double the disk space. Adding or removing a
       system oid column likewise requires rewriting the entire table.

       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. (These
       statements do not apply when dropping  the  system  oid  column;  that  is  done  with  an
       immediate rewrite.)

       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  might  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, and is required to do so for such 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 inheritance.


       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 SET DATA 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 and all its children:

       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 remove a check constraint from a table only:

       ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

       (The check constraint remains in place for any child tables.)

       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  forms  ADD, DROP, SET DEFAULT, and SET DATA TYPE (without USING) 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

       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.