Man Linux: Main Page and Category List


       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 [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 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

              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 [create_table(7)].

              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

              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

       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

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


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