granicus.if.org Git - postgresql/blob - doc/src/sgml/ref/alter_table.sgml

   1 <!--
   2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.59 2003/04/15 13:25:08 petere Exp $
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-ALTERTABLE">
   7  <refmeta>
   8   <refentrytitle id="sql-altertable-title">ALTER TABLE</refentrytitle>
   9   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  10  </refmeta>
  11
  12  <refnamediv>
  13   <refname>ALTER TABLE</refname>
  14   <refpurpose>change the definition of a table</refpurpose>
  15  </refnamediv>
  16
  17  <refsynopsisdiv>
  18 <synopsis>
  19 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
  20     ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
  21 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
  22     DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
  23 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
  24     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET DEFAULT <replaceable class="PARAMETER">value</replaceable> | DROP DEFAULT }
  25 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
  26     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
  27 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
  28     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
  29 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
  30     ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
  31 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
  32     SET WITHOUT OIDS
  33 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
  34     RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable
  35     class="PARAMETER">new_column</replaceable>
  36 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
  37     RENAME TO <replaceable class="PARAMETER">new_table</replaceable>
  38 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
  39     ADD <replaceable class="PARAMETER">table_constraint</replaceable>
  40 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ]
  41     DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
  42 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
  43     OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
  44 ALTER TABLE <replaceable class="PARAMETER">table</replaceable>
  45         CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
  46 </synopsis>
  47  </refsynopsisdiv>
  48
  49  <refsect1>
  50   <title>Description</title>
  51
  52   <para>
  53    <command>ALTER TABLE</command> changes the definition of an existing table.
  54    There are several subforms:
  55
  56   <variablelist>
  57    <varlistentry>
  58     <term><literal>ADD COLUMN</literal></term>
  59     <listitem>
  60      <para>
  61       This form adds a new column to the table using the same syntax as
  62       <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
  63      </para>
  64     </listitem>
  65    </varlistentry>
  66
  67    <varlistentry>
  68     <term><literal>DROP COLUMN</literal></term>
  69     <listitem>
  70      <para>
  71       This form drops a column from a table.  Indexes and
  72       table constraints involving the column will be automatically
  73       dropped as well.  You will need to say <literal>CASCADE</> if
  74       anything outside the table depends on the column, for example,
  75       foreign key references or views.
  76      </para>
  77     </listitem>
  78    </varlistentry>
  79
  80    <varlistentry>
  81     <term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
  82     <listitem>
  83      <para>
  84       These forms set or remove the default value for a column.
  85       The default values only apply to subsequent <command>INSERT</command>
  86       commands; they do not cause rows already in the table to change.
  87       Defaults may also be created for views, in which case they are
  88       inserted into <command>INSERT</> statements on the view before
  89       the view's <literal>ON INSERT</literal> rule is applied.
  90      </para>
  91     </listitem>
  92    </varlistentry>
  93
  94    <varlistentry>
  95     <term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term>
  96     <listitem>
  97      <para>
  98       These forms change whether a column is marked to allow null
  99       values or to reject null values.  You can only use <literal>SET
 100       NOT NULL</> when the column contains no null values.
 101      </para>
 102     </listitem>
 103    </varlistentry>
 104
 105    <varlistentry>
 106     <term><literal>SET STATISTICS</literal></term>
 107     <listitem>
 108      <para>
 109       This form
 110       sets the per-column statistics-gathering target for subsequent
 111       <xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
 112       The target can be set in the range 0 to 1000; alternatively, set it
 113       to -1 to revert to using the system default statistics target.
 114      </para>
 115     </listitem>
 116    </varlistentry>
 117
 118    <varlistentry>
 119     <term><literal>SET STORAGE</literal></term>
 120     <listitem>
 121      <para>
 122       This form sets the storage mode for a column. This controls whether this
 123       column is held inline or in a supplementary table, and whether the data
 124       should be compressed or not. <literal>PLAIN</literal> must be used
 125       for fixed-length values such as <type>integer</type> and is
 126       inline, uncompressed. <literal>MAIN</literal> is for inline,
 127       compressible data. <literal>EXTERNAL</literal> is for external,
 128       uncompressed data, and <literal>EXTENDED</literal> is for external,
 129       compressed data.  <literal>EXTENDED</literal> is the default for all
 130       data types that support it.  The use of <literal>EXTERNAL</literal> will, for example,
 131       make substring operations on a <type>text</type> column faster, at the penalty of
 132       increased storage space.
 133      </para>
 134     </listitem>
 135    </varlistentry>
 136
 137    <varlistentry>
 138     <term><literal>SET WITHOUT OIDS</literal></term>
 139     <listitem>
 140      <para>
 141       This form removes the <literal>oid</literal> column from the
 142       table.  Removing OIDs from a table does not occur immediately.
 143       The space that the OID uses will be reclaimed when the row is
 144       updated.  Without updating the row, both the space and the value
 145       of the OID are kept indefinitely.  This is semantically similar
 146       to the <literal>DROP COLUMN</literal> process.
 147      </para>
 148     </listitem>
 149    </varlistentry>
 150
 151    <varlistentry>
 152     <term><literal>RENAME</literal></term>
 153     <listitem>
 154      <para>
 155       The <literal>RENAME</literal> forms change the name of a table
 156       (or an index, sequence, or view) or the name of an individual column in
 157       a table. There is no effect on the stored data.
 158      </para>
 159     </listitem>
 160    </varlistentry>
 161
 162    <varlistentry>
 163     <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>
 164     <listitem>
 165      <para>
 166       This form adds a new constraint to a table using the same syntax as
 167       <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
 168      </para>
 169     </listitem>
 170    </varlistentry>
 171
 172    <varlistentry>
 173     <term><literal>DROP CONSTRAINT</literal></term>
 174     <listitem>
 175      <para>
 176       This form drops constraints on a table.
 177       Currently, constraints on tables are not required to have unique
 178       names, so there may be more than one constraint matching the specified
 179       name.  All such constraints will be dropped.
 180      </para>
 181     </listitem>
 182    </varlistentry>
 183
 184    <varlistentry>
 185     <term><literal>OWNER</literal></term>
 186     <listitem>
 187      <para>
 188       This form changes the owner of the table, index, sequence, or view to the
 189       specified user.
 190      </para>
 191     </listitem>
 192    </varlistentry>
 193
 194    <varlistentry>
 195     <term><literal>CLUSTER</literal></term>
 196     <listitem>
 197      <para>
 198       This form marks a table for future <xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
 199       operations.
 200      </para>
 201     </listitem>
 202    </varlistentry>
 203
 204   </variablelist>
 205   </para>
 206
 207   <para>
 208    You must own the table to use <command>ALTER TABLE</>; except for
 209    <command>ALTER TABLE OWNER</>, which may only be executed by a superuser.
 210   </para>
 211  </refsect1>
 212
 213  <refsect1>
 214   <title>Parameters</title>
 215
 216     <variablelist>
 217
 218      <varlistentry>
 219       <term><replaceable class="PARAMETER">table</replaceable></term>
 220       <listitem>
 221        <para>
 222         The name (possibly schema-qualified) of an existing table to
 223         alter. If <literal>ONLY</> is specified, only that table is
 224         altered. If <literal>ONLY</> is not specified, the table and all
 225         its descendant tables (if any) are updated. <literal>*</> can be
 226         appended to the table name to indicate that descendant tables are
 227         to be altered, but in the current version, this is the default
 228         behavior.  (In releases before 7.1, <literal>ONLY</> was the
 229         default behavior.  The default can be altered by changing the
 230         configuration parameter <varname>sql_inheritance</varname>.)
 231        </para>
 232       </listitem>
 233      </varlistentry>
 234
 235      <varlistentry>
 236       <term><replaceable class="PARAMETER">column</replaceable></term>
 237       <listitem>
 238        <para>
 239         Name of a new or existing column.
 240        </para>
 241       </listitem>
 242      </varlistentry>
 243
 244      <varlistentry>
 245       <term><replaceable class="PARAMETER">type</replaceable></term>
 246       <listitem>
 247        <para>
 248         Data type of the new column.
 249        </para>
 250       </listitem>
 251      </varlistentry>
 252
 253      <varlistentry>
 254       <term><replaceable class="PARAMETER">new_column</replaceable></term>
 255       <listitem>
 256        <para>
 257         New name for an existing column.
 258        </para>
 259       </listitem>
 260      </varlistentry>
 261
 262      <varlistentry>
 263       <term><replaceable class="PARAMETER">new_table</replaceable></term>
 264       <listitem>
 265        <para>
 266         New name for the table.
 267        </para>
 268       </listitem>
 269      </varlistentry>
 270
 271      <varlistentry>
 272       <term><replaceable class="PARAMETER">table_constraint</replaceable></term>
 273       <listitem>
 274        <para>
 275         New table constraint for the table.
 276        </para>
 277       </listitem>
 278      </varlistentry>
 279
 280      <varlistentry>
 281       <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
 282       <listitem>
 283        <para>
 284         Name of an existing constraint to drop.
 285        </para>
 286       </listitem>
 287      </varlistentry>
 288
 289      <varlistentry>
 290       <term><replaceable class="PARAMETER">new_owner</replaceable></term>
 291       <listitem>
 292        <para>
 293         The user name of the new owner of the table.
 294        </para>
 295       </listitem>
 296      </varlistentry>
 297
 298      <varlistentry>
 299       <term><replaceable class="PARAMETER">index_name</replaceable></term>
 300       <listitem>
 301        <para>
 302         The index name on which the table should be marked for clustering.
 303        </para>
 304       </listitem>
 305      </varlistentry>
 306
 307      <varlistentry>
 308       <term><literal>CASCADE</literal></term>
 309       <listitem>
 310        <para>
 311         Automatically drop objects that depend on the dropped column
 312         or constraint (for example, views referencing the column).
 313        </para>
 314       </listitem>
 315      </varlistentry>
 316
 317      <varlistentry>
 318       <term><literal>RESTRICT</literal></term>
 319       <listitem>
 320        <para>
 321         Refuse to drop the column or constraint if there are any dependent
 322         objects. This is the default behavior.
 323        </para>
 324       </listitem>
 325      </varlistentry>
 326
 327     </variablelist>
 328  </refsect1>
 329
 330  <refsect1>
 331   <title>Diagnostics</title>
 332
 333     <variablelist>
 334      <varlistentry>
 335       <term><computeroutput>ALTER TABLE</computeroutput></term>
 336       <listitem>
 337        <para>
 338         Message returned if successful.
 339        </para>
 340       </listitem>
 341      </varlistentry>
 342
 343      <varlistentry>
 344       <term><computeroutput>ERROR</computeroutput></term>
 345       <listitem>
 346        <para>
 347         Message returned if table or column does not exist.
 348        </para>
 349       </listitem>
 350      </varlistentry>
 351     </variablelist>
 352  </refsect1>
 353
 354  <refsect1>
 355   <title>Notes</title>
 356
 357    <para>
 358     The key word <literal>COLUMN</literal> is noise and can be omitted.
 359    </para>
 360
 361    <para>
 362     In the current implementation of <literal>ADD COLUMN</literal>,
 363     default and <literal>NOT NULL</> clauses for the new column are not supported.
 364     The new column always comes into being with all values null.
 365     You can use the <literal>SET DEFAULT</literal> form
 366     of <command>ALTER TABLE</command> to set the default afterward.
 367     (You may also want to update the already existing rows to the
 368     new default value, using
 369     <xref linkend="sql-update" endterm="sql-update-title">.)
 370     If you want to mark the column non-null, use the <literal>SET NOT NULL</>
 371     form after you've entered non-null values for the column in all rows.
 372    </para>
 373
 374    <para>
 375     The <literal>DROP COLUMN</literal> form does not physically remove
 376     the column, but simply makes it invisible to SQL operations.  Subsequent
 377     insert and update operations in the table will store a null value for the column.
 378     Thus, dropping a column is quick but it will not immediately reduce the
 379     on-disk size of your table, as the space occupied
 380     by the dropped column is not reclaimed.  The space will be
 381     reclaimed over time as existing rows are updated.
 382     To reclaim the space at once, do a dummy <command>UPDATE</> of all rows
 383     and then vacuum, as in:
 384 <programlisting>
 385 UPDATE table SET col = col;
 386 VACUUM FULL table;
 387 </programlisting>
 388    </para>
 389
 390    <para>
 391     If a table has any descendant tables, it is not permitted to add
 392     or rename a column in the parent table without doing the same to
 393     the descendants.  That is, <command>ALTER TABLE ONLY</command>
 394     will be rejected.  This ensures that the descendants always have
 395     columns matching the parent.
 396    </para>
 397
 398    <para>
 399     A recursive <literal>DROP COLUMN</literal> operation will remove a
 400     descendant table's column only if the descendant does not inherit
 401     that column from any other parents and never had an independent
 402     definition of the column.  A nonrecursive <literal>DROP
 403     COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
 404     COLUMN</command>) never removes any descendant columns, but
 405     instead marks them as independently defined rather than inherited.
 406    </para>
 407
 408    <para>
 409     Changing any part of a system catalog table is not permitted.
 410    </para>
 411
 412    <para>
 413     Refer to <command>CREATE TABLE</command> for a further description
 414     of valid parameters. <xref linkend="ddl"> has further information on
 415     inheritance.
 416    </para>
 417  </refsect1>
 418
 419  <refsect1>
 420   <title>Examples</title>
 421
 422   <para>
 423    To add a column of type <type>varchar</type> to a table:
 424 <programlisting>
 425 ALTER TABLE distributors ADD COLUMN address varchar(30);
 426 </programlisting>
 427   </para>
 428
 429   <para>
 430    To drop a column from a table:
 431 <programlisting>
 432 ALTER TABLE distributors DROP COLUMN address RESTRICT;
 433 </programlisting>
 434   </para>
 435
 436   <para>
 437    To rename an existing column:
 438 <programlisting>
 439 ALTER TABLE distributors RENAME COLUMN address TO city;
 440 </programlisting>
 441   </para>
 442
 443   <para>
 444    To rename an existing table:
 445 <programlisting>
 446 ALTER TABLE distributors RENAME TO suppliers;
 447 </programlisting>
 448   </para>
 449
 450   <para>
 451    To add a not-null constraint to a column:
 452 <programlisting>
 453 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
 454 </programlisting>
 455    To remove a not-null constraint from a column:
 456 <programlisting>
 457 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
 458 </programlisting>
 459   </para>
 460
 461   <para>
 462    To add a check constraint to a table:
 463 <programlisting>
 464 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
 465 </programlisting>
 466   </para>
 467
 468   <para>
 469    To remove a check constraint from a table and all its children:
 470 <programlisting>
 471 ALTER TABLE distributors DROP CONSTRAINT zipchk;
 472 </programlisting>
 473   </para>
 474
 475   <para>
 476    To add a foreign key constraint to a table:
 477 <programlisting>
 478 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
 479 </programlisting>
 480   </para>
 481
 482   <para>
 483    To add a (multicolumn) unique constraint to a table:
 484 <programlisting>
 485 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
 486 </programlisting>
 487   </para>
 488
 489   <para>
 490    To add an automatically named primary key constraint to a table, noting
 491    that a table can only ever have one primary key:
 492 <programlisting>
 493 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
 494 </programlisting>
 495   </para>
 496  </refsect1>
 497
 498  <refsect1>
 499   <title>Compatibility</title>
 500
 501   <para>
 502    The <literal>ADD COLUMN</literal> form conforms with the SQL
 503    standard, with the exception that it does not support defaults and
 504    not-null constraints, as explained above.  The <literal>ALTER
 505    COLUMN</literal> form is in full conformance.
 506   </para>
 507
 508   <para>
 509    The clauses to rename tables, columns, indexes, views, and sequences are
 510    <productname>PostgreSQL</productname> extensions of the SQL standard.
 511   </para>
 512
 513   <para>
 514    <command>ALTER TABLE DROP COLUMN</> can be used to drop the only
 515    column of a table, leaving a zero-column table.  This is an
 516    extension of SQL, which disallows zero-column tables.
 517   </para>
 518  </refsect1>
 519 </refentry>
 520
 521 <!-- Keep this comment at the end of the file
 522 Local variables:
 523 mode: sgml
 524 sgml-omittag:nil
 525 sgml-shorttag:t
 526 sgml-minimize-attributes:nil
 527 sgml-always-quote-attributes:t
 528 sgml-indent-step:1
 529 sgml-indent-data:t
 530 sgml-parent-document:nil
 531 sgml-default-dtd-file:"../reference.ced"
 532 sgml-exposed-tags:nil
 533 sgml-local-catalogs:"/usr/lib/sgml/catalog"
 534 sgml-local-ecat-files:nil
 535 End:
 536 -->