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