doc/src/sgml/ref/alter_table.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-ALTERTABLE">
   7  <refmeta>
   8   <refentrytitle id="sql-altertable-title">ALTER TABLE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>ALTER TABLE</refname>
  15   <refpurpose>change the definition of a table</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-altertable">
  19   <primary>ALTER TABLE</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
  25     <replaceable class="PARAMETER">action</replaceable> [, ... ]
  26 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
  27     RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable class="PARAMETER">new_column</replaceable>
  28 ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
  29     RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
  30 ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
  31     SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable>
  32
  33 where <replaceable class="PARAMETER">action</replaceable> is one of:
  34
  35     ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
  36     DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
  37     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ SET DATA ] TYPE <replaceable class="PARAMETER">type</replaceable> [ USING <replaceable class="PARAMETER">expression</replaceable> ]
  38     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
  39     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
  40     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
  41     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
  42     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
  43     ADD <replaceable class="PARAMETER">table_constraint</replaceable>
  44     DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
  45     DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
  46     ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
  47     ENABLE REPLICA TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
  48     ENABLE ALWAYS TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable>
  49     DISABLE RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
  50     ENABLE RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
  51     ENABLE REPLICA RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
  52     ENABLE ALWAYS RULE <replaceable class="PARAMETER">rewrite_rule_name</replaceable>
  53     CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
  54     SET WITHOUT CLUSTER
  55     SET WITHOUT OIDS
  56     SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
  57     RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )
  58     INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
  59     NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable>
  60     OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
  61     SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
  62 </synopsis>
  63  </refsynopsisdiv>
  64
  65  <refsect1>
  66   <title>Description</title>
  67
  68   <para>
  69    <command>ALTER TABLE</command> changes the definition of an existing table.
  70    There are several subforms:
  71
  72   <variablelist>
  73    <varlistentry>
  74     <term><literal>ADD COLUMN</literal></term>
  75     <listitem>
  76      <para>
  77       This form adds a new column to the table, using the same syntax as
  78       <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
  79      </para>
  80     </listitem>
  81    </varlistentry>
  82
  83    <varlistentry>
  84     <term><literal>DROP COLUMN</literal></term>
  85     <listitem>
  86      <para>
  87       This form drops a column from a table.  Indexes and
  88       table constraints involving the column will be automatically
  89       dropped as well.  You will need to say <literal>CASCADE</> if
  90       anything outside the table depends on the column, for example,
  91       foreign key references or views.
  92      </para>
  93     </listitem>
  94    </varlistentry>
  95
  96    <varlistentry>
  97     <term><literal>SET DATA TYPE</literal></term>
  98     <listitem>
  99      <para>
 100       This form changes the type of a column of a table. Indexes and
 101       simple table constraints involving the column will be automatically
 102       converted to use the new column type by reparsing the originally
 103       supplied expression.  The optional <literal>USING</literal>
 104       clause specifies how to compute the new column value from the old;
 105       if omitted, the default conversion is the same as an assignment
 106       cast from old data type to new.  A  <literal>USING</literal>
 107       clause must be provided if there is no implicit or assignment
 108       cast from old to new type.
 109      </para>
 110     </listitem>
 111    </varlistentry>
 112
 113    <varlistentry>
 114     <term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
 115     <listitem>
 116      <para>
 117       These forms set or remove the default value for a column.
 118       The default values only apply to subsequent <command>INSERT</command>
 119       commands; they do not cause rows already in the table to change.
 120       Defaults can also be created for views, in which case they are
 121       inserted into <command>INSERT</> statements on the view before
 122       the view's <literal>ON INSERT</literal> rule is applied.
 123      </para>
 124     </listitem>
 125    </varlistentry>
 126
 127    <varlistentry>
 128     <term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term>
 129     <listitem>
 130      <para>
 131       These forms change whether a column is marked to allow null
 132       values or to reject null values.  You can only use <literal>SET
 133       NOT NULL</> when the column contains no null values.
 134      </para>
 135     </listitem>
 136    </varlistentry>
 137
 138    <varlistentry>
 139     <term><literal>SET STATISTICS</literal></term>
 140     <listitem>
 141      <para>
 142       This form
 143       sets the per-column statistics-gathering target for subsequent
 144       <xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
 145       The target can be set in the range 0 to 10000; alternatively, set it
 146       to -1 to revert to using the system default statistics
 147       target (<xref linkend="guc-default-statistics-target">).
 148       For more information on the use of statistics by the
 149       <productname>PostgreSQL</productname> query planner, refer to
 150       <xref linkend="planner-stats">.
 151      </para>
 152     </listitem>
 153    </varlistentry>
 154
 155    <varlistentry>
 156     <indexterm>
 157      <primary>TOAST</primary>
 158      <secondary>per-column storage settings</secondary>
 159     </indexterm>
 160
 161     <term><literal>SET STORAGE</literal></term>
 162     <listitem>
 163      <para>
 164       This form sets the storage mode for a column. This controls whether this
 165       column is held inline or in a supplementary table, and whether the data
 166       should be compressed or not. <literal>PLAIN</literal> must be used
 167       for fixed-length values such as <type>integer</type> and is
 168       inline, uncompressed. <literal>MAIN</literal> is for inline,
 169       compressible data. <literal>EXTERNAL</literal> is for external,
 170       uncompressed data, and <literal>EXTENDED</literal> is for external,
 171       compressed data.  <literal>EXTENDED</literal> is the default for most
 172       data types that support non-<literal>PLAIN</literal> storage.
 173       Use of <literal>EXTERNAL</literal> will
 174       make substring operations on <type>text</type> and <type>bytea</type>
 175       columns faster, at the penalty of increased storage space.  Note that
 176       <literal>SET STORAGE</> doesn't itself change anything in the table,
 177       it just sets the strategy to be pursued during future table updates.
 178       See <xref linkend="storage-toast"> for more information.
 179      </para>
 180     </listitem>
 181    </varlistentry>
 182
 183    <varlistentry>
 184     <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>
 185     <listitem>
 186      <para>
 187       This form adds a new constraint to a table using the same syntax as
 188       <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
 189      </para>
 190     </listitem>
 191    </varlistentry>
 192
 193    <varlistentry>
 194     <term><literal>DROP CONSTRAINT</literal></term>
 195     <listitem>
 196      <para>
 197       This form drops the specified constraint on a table.
 198      </para>
 199     </listitem>
 200    </varlistentry>
 201
 202    <varlistentry>
 203     <term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] TRIGGER</literal></term>
 204     <listitem>
 205      <para>
 206       These forms configure the firing of trigger(s) belonging to the table.
 207       A disabled trigger is still known to the system, but is not executed
 208       when its triggering event occurs.  For a deferred trigger, the enable
 209       status is checked when the event occurs, not when the trigger function
 210       is actually executed.  One can disable or enable a single
 211       trigger specified by name, or all triggers on the table, or only
 212       user triggers (this option excludes triggers that are used to implement
 213       foreign key constraints).  Disabling or enabling constraint triggers
 214       requires superuser privileges; it should be done with caution since
 215       of course the integrity of the constraint cannot be guaranteed if the
 216       triggers are not executed.
 217       The trigger firing mechanism is also affected by the configuration
 218       variable <xref linkend="guc-session-replication-role">. Simply enabled
 219       triggers will fire when the replication role is <quote>origin</>
 220       (the default) or <quote>local</>. Triggers configured <literal>ENABLE REPLICA</literal>
 221       will only fire if the session is in <quote>replica</> mode and triggers
 222       configured <literal>ENABLE ALWAYS</literal> will fire regardless of the current replication
 223       mode.
 224      </para>
 225     </listitem>
 226    </varlistentry>
 227
 228    <varlistentry>
 229     <term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] RULE</literal></term>
 230     <listitem>
 231      <para>
 232       These forms configure the firing of rewrite rules belonging to the table.
 233       A disabled rule is still known to the system, but is not applied
 234       during query rewriting. The semantics are as for disabled/enabled
 235       triggers. This configuration is ignored for <literal>ON SELECT</literal> rules, which
 236       are always applied in order to keep views working even if the current
 237       session is in a non-default replication role.
 238      </para>
 239     </listitem>
 240    </varlistentry>
 241
 242    <varlistentry>
 243     <term><literal>CLUSTER</literal></term>
 244     <listitem>
 245      <para>
 246       This form selects the default index for future
 247       <xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
 248       operations.  It does not actually re-cluster the table.
 249      </para>
 250     </listitem>
 251    </varlistentry>
 252
 253    <varlistentry>
 254     <term><literal>SET WITHOUT CLUSTER</literal></term>
 255     <listitem>
 256      <para>
 257       This form removes the most recently used
 258       <xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
 259       index specification from the table.  This affects
 260       future cluster operations that don't specify an index.
 261      </para>
 262     </listitem>
 263    </varlistentry>
 264
 265    <varlistentry>
 266     <term><literal>SET WITHOUT OIDS</literal></term>
 267     <listitem>
 268      <para>
 269       This form removes the <literal>oid</literal> system column from the
 270       table.  This is exactly equivalent to
 271       <literal>DROP COLUMN oid RESTRICT</literal>,
 272       except that it will not complain if there is already no
 273       <literal>oid</literal> column.
 274      </para>
 275
 276      <para>
 277       Note that there is no variant of <command>ALTER TABLE</command>
 278       that allows OIDs to be restored to a table once they have been
 279       removed.
 280      </para>
 281     </listitem>
 282    </varlistentry>
 283
 284    <varlistentry>
 285     <term><literal>SET ( <replaceable class="PARAMETER">storage_parameter</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term>
 286     <listitem>
 287      <para>
 288       This form changes one or more storage parameters for the table.  See
 289       <xref linkend="SQL-CREATETABLE-storage-parameters"
 290       endterm="sql-createtable-storage-parameters-title">
 291       for details on the available parameters.  Note that the table contents
 292       will not be modified immediately by this command; depending on the
 293       parameter you might need to rewrite the table to get the desired effects.
 294       That can be done with <xref linkend="SQL-CLUSTER"
 295       endterm="sql-cluster-title"> or one of the forms of <command>ALTER
 296       TABLE</> that forces a table rewrite.
 297      </para>
 298
 299      <note>
 300       <para>
 301        While <command>CREATE TABLE</> allows <literal>OIDS</> to be specified
 302        in the <literal>WITH (<replaceable
 303        class="PARAMETER">storage_parameter</>)</literal> syntax,
 304        <command>ALTER TABLE</> does not treat <literal>OIDS</> as a
 305        storage parameter.
 306       </para>
 307      </note>
 308     </listitem>
 309    </varlistentry>
 310
 311    <varlistentry>
 312     <term><literal>RESET ( <replaceable class="PARAMETER">storage_parameter</replaceable> [, ... ] )</literal></term>
 313     <listitem>
 314      <para>
 315       This form resets one or more storage parameters to their
 316       defaults.  As with <literal>SET</>, a table rewrite might be
 317       needed to update the table entirely.
 318      </para>
 319     </listitem>
 320    </varlistentry>
 321
 322    <varlistentry>
 323     <term><literal>INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>
 324     <listitem>
 325      <para>
 326       This form adds the target table as a new child of the specified parent
 327       table.  Subsequently, queries against the parent will include records
 328       of the target table.  To be added as a child, the target table must
 329       already contain all the same columns as the parent (it could have
 330       additional columns, too).  The columns must have matching data types,
 331       and if they have <literal>NOT NULL</literal> constraints in the parent
 332       then they must also have <literal>NOT NULL</literal> constraints in the
 333       child.
 334      </para>
 335
 336      <para>
 337       There must also be matching child-table constraints for all
 338       <literal>CHECK</literal> constraints of the parent. Currently
 339       <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and
 340       <literal>FOREIGN KEY</literal> constraints are not considered, but
 341       this might change in the future.
 342      </para>
 343     </listitem>
 344    </varlistentry>
 345
 346    <varlistentry>
 347     <term><literal>NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term>
 348     <listitem>
 349      <para>
 350       This form removes the target table from the list of children of the
 351       specified parent table.
 352       Queries against the parent table will no longer include records drawn
 353       from the target table.
 354      </para>
 355     </listitem>
 356    </varlistentry>
 357
 358    <varlistentry>
 359     <term><literal>OWNER</literal></term>
 360     <listitem>
 361      <para>
 362       This form changes the owner of the table, sequence, or view to the
 363       specified user.
 364      </para>
 365     </listitem>
 366    </varlistentry>
 367
 368    <varlistentry>
 369     <term><literal>SET TABLESPACE</literal></term>
 370     <listitem>
 371      <para>
 372       This form changes the table's tablespace to the specified tablespace and
 373       moves the data file(s) associated with the table to the new tablespace.
 374       Indexes on the table, if any, are not moved; but they can be moved
 375       separately with additional <literal>SET TABLESPACE</literal> commands.
 376       See also
 377       <xref linkend="SQL-CREATETABLESPACE" endterm="sql-createtablespace-title">.
 378      </para>
 379     </listitem>
 380    </varlistentry>
 381
 382    <varlistentry>
 383     <term><literal>RENAME</literal></term>
 384     <listitem>
 385      <para>
 386       The <literal>RENAME</literal> forms change the name of a table
 387       (or an index, sequence, or view) or the name of an individual column in
 388       a table. There is no effect on the stored data.
 389      </para>
 390     </listitem>
 391    </varlistentry>
 392
 393    <varlistentry>
 394     <term><literal>SET SCHEMA</literal></term>
 395     <listitem>
 396      <para>
 397       This form moves the table into another schema.  Associated indexes,
 398       constraints, and sequences owned by table columns are moved as well.
 399      </para>
 400     </listitem>
 401    </varlistentry>
 402
 403   </variablelist>
 404   </para>
 405
 406   <para>
 407    All the actions except <literal>RENAME</literal> and <literal>SET SCHEMA</>
 408    can be combined into
 409    a list of multiple alterations to apply in parallel.  For example, it
 410    is possible to add several columns and/or alter the type of several
 411    columns in a single command.  This is particularly useful with large
 412    tables, since only one pass over the table need be made.
 413   </para>
 414
 415   <para>
 416    You must own the table to use <command>ALTER TABLE</>.
 417    To change the schema of a table, you must also have
 418    <literal>CREATE</literal> privilege on the new schema.
 419    To add the table as a new child of a parent table, you must own the
 420    parent table as well.
 421    To alter the owner, you must also be a direct or indirect member of the new
 422    owning role, and that role must have <literal>CREATE</literal> privilege on
 423    the table's schema.  (These restrictions enforce that altering the owner
 424    doesn't do anything you couldn't do by dropping and recreating the table.
 425    However, a superuser can alter ownership of any table anyway.)
 426   </para>
 427  </refsect1>
 428
 429  <refsect1>
 430   <title>Parameters</title>
 431
 432     <variablelist>
 433
 434      <varlistentry>
 435       <term><replaceable class="PARAMETER">name</replaceable></term>
 436       <listitem>
 437        <para>
 438         The name (possibly schema-qualified) of an existing table to
 439         alter. If <literal>ONLY</> is specified, only that table is
 440         altered. If <literal>ONLY</> is not specified, the table and all
 441         its descendant tables (if any) are updated. <literal>*</> can be
 442         appended to the table name to indicate that descendant tables are
 443         to be altered, but in the current version, this is the default
 444         behavior.  (In releases before 7.1, <literal>ONLY</> was the
 445         default behavior.  The default can be altered by changing the
 446         configuration parameter <xref linkend="guc-sql-inheritance">.)
 447        </para>
 448       </listitem>
 449      </varlistentry>
 450
 451      <varlistentry>
 452       <term><replaceable class="PARAMETER">column</replaceable></term>
 453       <listitem>
 454        <para>
 455         Name of a new or existing column.
 456        </para>
 457       </listitem>
 458      </varlistentry>
 459
 460      <varlistentry>
 461       <term><replaceable class="PARAMETER">new_column</replaceable></term>
 462       <listitem>
 463        <para>
 464         New name for an existing column.
 465        </para>
 466       </listitem>
 467      </varlistentry>
 468
 469      <varlistentry>
 470       <term><replaceable class="PARAMETER">new_name</replaceable></term>
 471       <listitem>
 472        <para>
 473         New name for the table.
 474        </para>
 475       </listitem>
 476      </varlistentry>
 477
 478      <varlistentry>
 479       <term><replaceable class="PARAMETER">type</replaceable></term>
 480       <listitem>
 481        <para>
 482         Data type of the new column, or new data type for an existing
 483         column.
 484        </para>
 485       </listitem>
 486      </varlistentry>
 487
 488      <varlistentry>
 489       <term><replaceable class="PARAMETER">table_constraint</replaceable></term>
 490       <listitem>
 491        <para>
 492         New table constraint for the table.
 493        </para>
 494       </listitem>
 495      </varlistentry>
 496
 497      <varlistentry>
 498       <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
 499       <listitem>
 500        <para>
 501         Name of an existing constraint to drop.
 502        </para>
 503       </listitem>
 504      </varlistentry>
 505
 506      <varlistentry>
 507       <term><literal>CASCADE</literal></term>
 508       <listitem>
 509        <para>
 510         Automatically drop objects that depend on the dropped column
 511         or constraint (for example, views referencing the column).
 512        </para>
 513       </listitem>
 514      </varlistentry>
 515
 516      <varlistentry>
 517       <term><literal>RESTRICT</literal></term>
 518       <listitem>
 519        <para>
 520         Refuse to drop the column or constraint if there are any dependent
 521         objects. This is the default behavior.
 522        </para>
 523       </listitem>
 524      </varlistentry>
 525
 526      <varlistentry>
 527       <term><replaceable class="PARAMETER">trigger_name</replaceable></term>
 528       <listitem>
 529        <para>
 530         Name of a single trigger to disable or enable.
 531        </para>
 532       </listitem>
 533      </varlistentry>
 534
 535      <varlistentry>
 536       <term><literal>ALL</literal></term>
 537       <listitem>
 538        <para>
 539         Disable or enable all triggers belonging to the table.
 540         (This requires superuser privilege if any of the triggers are for
 541         foreign key constraints.)
 542        </para>
 543       </listitem>
 544      </varlistentry>
 545
 546      <varlistentry>
 547       <term><literal>USER</literal></term>
 548       <listitem>
 549        <para>
 550         Disable or enable all triggers belonging to the table except for
 551         foreign key constraint triggers.
 552        </para>
 553       </listitem>
 554      </varlistentry>
 555
 556      <varlistentry>
 557       <term><replaceable class="PARAMETER">index_name</replaceable></term>
 558       <listitem>
 559        <para>
 560         The index name on which the table should be marked for clustering.
 561        </para>
 562       </listitem>
 563      </varlistentry>
 564
 565      <varlistentry>
 566       <term><replaceable class="PARAMETER">storage_parameter</replaceable></term>
 567       <listitem>
 568        <para>
 569         The name of a table storage parameter.
 570        </para>
 571       </listitem>
 572      </varlistentry>
 573
 574      <varlistentry>
 575       <term><replaceable class="PARAMETER">value</replaceable></term>
 576       <listitem>
 577        <para>
 578         The new value for a table storage parameter.
 579         This might be a number or a word depending on the parameter.
 580        </para>
 581       </listitem>
 582      </varlistentry>
 583
 584      <varlistentry>
 585       <term><replaceable class="PARAMETER">parent_table</replaceable></term>
 586       <listitem>
 587        <para>
 588         A parent table to associate or de-associate with this table.
 589        </para>
 590       </listitem>
 591      </varlistentry>
 592
 593      <varlistentry>
 594       <term><replaceable class="PARAMETER">new_owner</replaceable></term>
 595       <listitem>
 596        <para>
 597         The user name of the new owner of the table.
 598        </para>
 599       </listitem>
 600      </varlistentry>
 601
 602      <varlistentry>
 603       <term><replaceable class="PARAMETER">new_tablespace</replaceable></term>
 604       <listitem>
 605        <para>
 606         The name of the tablespace to which the table will be moved.
 607        </para>
 608       </listitem>
 609      </varlistentry>
 610
 611      <varlistentry>
 612       <term><replaceable class="PARAMETER">new_schema</replaceable></term>
 613       <listitem>
 614        <para>
 615         The name of the schema to which the table will be moved.
 616        </para>
 617       </listitem>
 618      </varlistentry>
 619
 620     </variablelist>
 621  </refsect1>
 622
 623  <refsect1>
 624   <title>Notes</title>
 625
 626    <para>
 627     The key word <literal>COLUMN</literal> is noise and can be omitted.
 628    </para>
 629
 630    <para>
 631     When a column is added with <literal>ADD COLUMN</literal>, all existing
 632     rows in the table are initialized with the column's default value
 633     (NULL if no <literal>DEFAULT</> clause is specified).
 634    </para>
 635
 636    <para>
 637     Adding a column with a non-null default or changing the type of an
 638     existing column will require the entire table to be rewritten.  This
 639     might take a significant amount of time for a large table; and it will
 640     temporarily require double the disk space.
 641    </para>
 642
 643    <para>
 644     Adding a <literal>CHECK</> or <literal>NOT NULL</> constraint requires
 645     scanning the table to verify that existing rows meet the constraint.
 646    </para>
 647
 648    <para>
 649     The main reason for providing the option to specify multiple changes
 650     in a single <command>ALTER TABLE</> is that multiple table scans or
 651     rewrites can thereby be combined into a single pass over the table.
 652    </para>
 653
 654    <para>
 655     The <literal>DROP COLUMN</literal> form does not physically remove
 656     the column, but simply makes it invisible to SQL operations.  Subsequent
 657     insert and update operations in the table will store a null value for the
 658     column. Thus, dropping a column is quick but it will not immediately
 659     reduce the on-disk size of your table, as the space occupied
 660     by the dropped column is not reclaimed.  The space will be
 661     reclaimed over time as existing rows are updated.
 662    </para>
 663
 664    <para>
 665     The fact that <literal>ALTER TYPE</> requires rewriting the whole table
 666     is sometimes an advantage, because the rewriting process eliminates
 667     any dead space in the table.  For example, to reclaim the space occupied
 668     by a dropped column immediately, the fastest way is:
 669 <programlisting>
 670 ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
 671 </programlisting>
 672     where <literal>anycol</> is any remaining table column and
 673     <literal>anytype</> is the same type that column already has.
 674     This results in no semantically-visible change in the table,
 675     but the command forces rewriting, which gets rid of no-longer-useful
 676     data.
 677    </para>
 678
 679    <para>
 680     The <literal>USING</literal> option of <literal>ALTER TYPE</> can actually
 681     specify any expression involving the old values of the row; that is, it
 682     can refer to other columns as well as the one being converted.  This allows
 683     very general conversions to be done with the <literal>ALTER TYPE</>
 684     syntax.  Because of this flexibility, the <literal>USING</literal>
 685     expression is not applied to the column's default value (if any); the
 686     result might not be a constant expression as required for a default.
 687     This means that when there is no implicit or assignment cast from old to
 688     new type, <literal>ALTER TYPE</> might fail to convert the default even
 689     though a <literal>USING</literal> clause is supplied.  In such cases,
 690     drop the default with <literal>DROP DEFAULT</>, perform the <literal>ALTER
 691     TYPE</>, and then use <literal>SET DEFAULT</> to add a suitable new
 692     default.  Similar considerations apply to indexes and constraints involving
 693     the column.
 694    </para>
 695
 696    <para>
 697     If a table has any descendant tables, it is not permitted to add,
 698     rename, or change the type of a column in the parent table without doing
 699     the same to the descendants.  That is, <command>ALTER TABLE ONLY</command>
 700     will be rejected.  This ensures that the descendants always have
 701     columns matching the parent.
 702    </para>
 703
 704    <para>
 705     A recursive <literal>DROP COLUMN</literal> operation will remove a
 706     descendant table's column only if the descendant does not inherit
 707     that column from any other parents and never had an independent
 708     definition of the column.  A nonrecursive <literal>DROP
 709     COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
 710     COLUMN</command>) never removes any descendant columns, but
 711     instead marks them as independently defined rather than inherited.
 712    </para>
 713
 714    <para>
 715     The <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>,
 716     and <literal>TABLESPACE</> actions never recurse to descendant tables;
 717     that is, they always act as though <literal>ONLY</> were specified.
 718     Adding a constraint can recurse only for <literal>CHECK</> constraints,
 719     and is required to do so for such constraints.
 720    </para>
 721
 722    <para>
 723     Changing any part of a system catalog table is not permitted.
 724    </para>
 725
 726    <para>
 727     Refer to <xref linkend="sql-createtable"
 728     endterm="sql-createtable-title"> for a further description of valid
 729     parameters. <xref linkend="ddl"> has further information on
 730     inheritance.
 731    </para>
 732  </refsect1>
 733
 734  <refsect1>
 735   <title>Examples</title>
 736
 737   <para>
 738    To add a column of type <type>varchar</type> to a table:
 739 <programlisting>
 740 ALTER TABLE distributors ADD COLUMN address varchar(30);
 741 </programlisting>
 742   </para>
 743
 744   <para>
 745    To drop a column from a table:
 746 <programlisting>
 747 ALTER TABLE distributors DROP COLUMN address RESTRICT;
 748 </programlisting>
 749   </para>
 750
 751   <para>
 752    To change the types of two existing columns in one operation:
 753 <programlisting>
 754 ALTER TABLE distributors
 755     ALTER COLUMN address TYPE varchar(80),
 756     ALTER COLUMN name TYPE varchar(100);
 757 </programlisting>
 758   </para>
 759
 760   <para>
 761    To change an integer column containing UNIX timestamps to <type>timestamp
 762    with time zone</type> via a <literal>USING</literal> clause:
 763 <programlisting>
 764 ALTER TABLE foo
 765     ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
 766     USING
 767         timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
 768 </programlisting>
 769   </para>
 770
 771   <para>
 772    The same, when the column has a default expression that won't automatically
 773    cast to the new data type:
 774 <programlisting>
 775 ALTER TABLE foo
 776     ALTER COLUMN foo_timestamp DROP DEFAULT,
 777     ALTER COLUMN foo_timestamp TYPE timestamp with time zone
 778     USING
 779         timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
 780     ALTER COLUMN foo_timestamp SET DEFAULT now();
 781 </programlisting>
 782   </para>
 783
 784   <para>
 785    To rename an existing column:
 786 <programlisting>
 787 ALTER TABLE distributors RENAME COLUMN address TO city;
 788 </programlisting>
 789   </para>
 790
 791   <para>
 792    To rename an existing table:
 793 <programlisting>
 794 ALTER TABLE distributors RENAME TO suppliers;
 795 </programlisting>
 796   </para>
 797
 798   <para>
 799    To add a not-null constraint to a column:
 800 <programlisting>
 801 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
 802 </programlisting>
 803    To remove a not-null constraint from a column:
 804 <programlisting>
 805 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
 806 </programlisting>
 807   </para>
 808
 809   <para>
 810    To add a check constraint to a table and all its children:
 811 <programlisting>
 812 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
 813 </programlisting>
 814   </para>
 815
 816   <para>
 817    To remove a check constraint from a table and all its children:
 818 <programlisting>
 819 ALTER TABLE distributors DROP CONSTRAINT zipchk;
 820 </programlisting>
 821   </para>
 822
 823   <para>
 824    To remove a check constraint from a table only:
 825 <programlisting>
 826 ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
 827 </programlisting>
 828    (The check constraint remains in place for any child tables.)
 829   </para>
 830
 831   <para>
 832    To add a foreign key constraint to a table:
 833 <programlisting>
 834 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
 835 </programlisting>
 836   </para>
 837
 838   <para>
 839    To add a (multicolumn) unique constraint to a table:
 840 <programlisting>
 841 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
 842 </programlisting>
 843   </para>
 844
 845   <para>
 846    To add an automatically named primary key constraint to a table, noting
 847    that a table can only ever have one primary key:
 848 <programlisting>
 849 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
 850 </programlisting>
 851   </para>
 852
 853   <para>
 854    To move a table to a different tablespace:
 855 <programlisting>
 856 ALTER TABLE distributors SET TABLESPACE fasttablespace;
 857 </programlisting>
 858   </para>
 859
 860   <para>
 861    To move a table to a different schema:
 862 <programlisting>
 863 ALTER TABLE myschema.distributors SET SCHEMA yourschema;
 864 </programlisting>
 865   </para>
 866
 867  </refsect1>
 868
 869  <refsect1>
 870   <title>Compatibility</title>
 871
 872   <para>
 873    The forms <literal>ADD</literal>, <literal>DROP</>, <literal>SET DEFAULT</>,
 874    and <literal>SET DATA TYPE</literal> (without <literal>USING</literal>)
 875    conform with the SQL standard.  The other forms are
 876    <productname>PostgreSQL</productname> extensions of the SQL standard.
 877    Also, the ability to specify more than one manipulation in a single
 878    <command>ALTER TABLE</> command is an extension.
 879   </para>
 880
 881   <para>
 882    <command>ALTER TABLE DROP COLUMN</> can be used to drop the only
 883    column of a table, leaving a zero-column table.  This is an
 884    extension of SQL, which disallows zero-column tables.
 885   </para>
 886  </refsect1>
 887 </refentry>