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