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