Man Pages

alter_table(7) - phpMan alter_table(7) - phpMan

Command: man perldoc info search(apropos)  


ALTER TABLE(7)                   SQL Commands                   ALTER TABLE(7)



NAME
       ALTER TABLE - change the definition of a table


SYNOPSIS
       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
           ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
           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 WITHOUT CLUSTER
           SET WITH OIDS
           SET WITHOUT OIDS
           SET ( storage_parameter = value [, ... ] )
           RESET ( storage_parameter [, ... ] )
           INHERIT parent_table
           NO INHERIT parent_table
           OWNER TO new_owner
           SET TABLESPACE new_tablespace


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

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

       DROP COLUMN
              This  form drops a column from a table. Indexes and table constraints involving the column will be auto-
              matically 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.

       SET DATA TYPE
              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.

       SET/DROP DEFAULT
              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.

       SET/DROP NOT NULL
              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.

       SET STATISTICS
              This  form  sets  the  per-column statistics-gathering target for subsequent ANALYZE [analyze(7)] opera-
              tions.  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.


       SET STORAGE
              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. EXTER-
              NAL  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  opera-
              tions  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)].

       DROP CONSTRAINT
              This form drops the specified constraint on a table.

       DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
              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 trig-
              ger 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  config-
              ured as ENABLE ALWAYS will fire regardless of the current replication mode.

       DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
              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  dis-
              abled/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.

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

       SET WITHOUT CLUSTER
              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.

       SET WITH OIDS
              This form adds an oid system column to the table (see in the documentation).  It does nothing if the ta-
              ble 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.

       SET WITHOUT OIDS
              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.

       SET TABLESPACE
              This  form changes the table's tablespace to the specified tablespace and moves the data file(s) associ-
              ated 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  [cre-
              ate_tablespace(7)].

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

       SET SCHEMA
              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 sin-
       gle 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  privi-
       lege  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.)

PARAMETERS
       name   The  name  (optionally  schema-qualified) of an existing table to alter. If ONLY is specified before the
              table name, only that table is altered. If ONLY is not specified,  the  table  and  all  its  descendant
              tables  (if any) are altered. Optionally, * can be specified after the table name to explicitly indicate
              that descendant tables are included.

       column Name of a new or existing column.

       new_column
              New name for an existing column.

       new_name
              New name for the table.

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

       table_constraint
              New table constraint for the table.

       constraint_name
              Name of an existing constraint to drop.

       CASCADE
              Automatically drop objects that depend on the dropped column or constraint (for example, views referenc-
              ing the column).

       RESTRICT
              Refuse  to  drop the column or constraint if there are any dependent objects. This is the default behav-
              ior.

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

       index_name
              The index name on which the table should be marked for clustering.

       storage_parameter
              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 parame-
              ter.

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

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

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

       new_schema
              The name of the schema to which the table will be moved.

NOTES
       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 ta-
       ble 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  nonrecur-
       sive  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.

EXAMPLES
       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
           USING
               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
           USING
               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 one 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;


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



SQL - Language Statements         2014-02-17                    ALTER TABLE(7)