2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.50 2002/09/21 18:32:54 petere Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-ALTERTABLE">
8 <refentrytitle id="sql-altertable-title">ALTER TABLE</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
16 change the definition of a table
21 <date>1999-07-20</date>
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 <replaceable class="PARAMETER">table</replaceable>
42 ADD <replaceable class="PARAMETER">table_constraint_definition</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>
49 <refsect2 id="R2-SQL-ALTERTABLE-1">
51 <date>1998-04-15</date>
60 <term><replaceable class="PARAMETER"> table </replaceable></term>
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.
77 <term><replaceable class="PARAMETER"> column </replaceable></term>
80 Name of a new or existing column.
86 <term><replaceable class="PARAMETER"> type </replaceable></term>
89 Type of the new column.
95 <term><replaceable class="PARAMETER"> new_column </replaceable></term>
98 New name for an existing column.
104 <term><replaceable class="PARAMETER"> new_table </replaceable></term>
107 New name for the table.
113 <term><replaceable class="PARAMETER"> table_constraint_definition </replaceable></term>
116 New table constraint for the table.
122 <term><replaceable class="PARAMETER"> constraint_name </replaceable></term>
125 Name of an existing constraint to drop.
131 <term><replaceable class="PARAMETER">new_owner </replaceable></term>
134 The user name of the new owner of the table.
143 Automatically drop objects that depend on the dropped column
144 or constraint (for example, views referencing the column).
150 <term>RESTRICT</term>
153 Refuse to drop the column or constraint if there are any dependent
154 objects. This is the default behavior.
163 <refsect2 id="R2-SQL-ALTERTABLE-2">
165 <date>1998-04-15</date>
174 <term><computeroutput>ALTER TABLE</computeroutput></term>
177 Message returned from column or table renaming.
183 <term><computeroutput>ERROR</computeroutput></term>
186 Message returned if table or column is not available.
195 <refsect1 id="R1-SQL-ALTERTABLE-1">
197 <date>1998-04-15</date>
203 <command>ALTER TABLE</command> changes the definition of an existing table.
204 There are several sub-forms:
210 <term>ADD COLUMN</term>
213 This form adds a new column to the table using the same syntax as
214 <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
220 <term>DROP COLUMN</term>
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.
233 <term>SET/DROP DEFAULT</term>
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.
247 <term>SET/DROP NOT NULL</term>
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.
258 <term>SET STATISTICS</term>
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.
271 <term>SET STORAGE</term>
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.
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.
301 <term>ADD <replaceable class="PARAMETER">table_constraint_definition</replaceable></term>
304 This form adds a new constraint to a table using the same syntax as
305 <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
311 <term>DROP CONSTRAINT</term>
314 This form drops constraints on a table (and its children).
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.
326 This form changes the owner of the table, index, sequence or view to the
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.
339 <refsect2 id="R2-SQL-ALTERTABLE-3">
341 <date>1998-04-15</date>
347 The keyword <literal>COLUMN</literal> is noise and can be omitted.
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.
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:
374 UPDATE table SET col = col;
380 Changing any part of the schema of a system
381 catalog is not permitted.
385 Refer to <command>CREATE TABLE</command> for a further description
387 The <citetitle>PostgreSQL User's Guide</citetitle> has further
388 information on inheritance.
393 <refsect1 id="R1-SQL-ALTERTABLE-2">
398 To add a column of type <type>varchar</type> to a table:
400 ALTER TABLE distributors ADD COLUMN address VARCHAR(30);
405 To drop a column from a table:
407 ALTER TABLE distributors DROP COLUMN address RESTRICT;
412 To rename an existing column:
414 ALTER TABLE distributors RENAME COLUMN address TO city;
419 To rename an existing table:
421 ALTER TABLE distributors RENAME TO suppliers;
426 To add a NOT NULL constraint to a column:
428 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
430 To remove a NOT NULL constraint from a column:
432 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
437 To add a check constraint to a table:
439 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
444 To remove a check constraint from a table and all its children:
446 ALTER TABLE distributors DROP CONSTRAINT zipchk;
451 To add a foreign key constraint to a table:
453 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL;
458 To add a (multicolumn) unique constraint to a table:
460 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
465 To add an automatically named primary key constraint to a table, noting
466 that a table can only ever have one primary key:
468 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
473 <refsect1 id="R1-SQL-ALTERTABLE-3">
478 <refsect2 id="R2-SQL-ALTERTABLE-4">
480 <date>1998-04-15</date>
484 The <literal>ADD COLUMN</literal> form is compliant with the exception that
485 it does not support defaults and NOT NULL constraints, as explained above.
486 The <literal>ALTER COLUMN</literal> form is in full compliance.
490 The clauses to rename tables, columns, indexes, and sequences are
491 <productname>PostgreSQL</productname> extensions from SQL92.
498 <!-- Keep this comment at the end of the file
503 sgml-minimize-attributes:nil
504 sgml-always-quote-attributes:t
507 sgml-parent-document:nil
508 sgml-default-dtd-file:"../reference.ced"
509 sgml-exposed-tags:nil
510 sgml-local-catalogs:"/usr/lib/sgml/catalog"
511 sgml-local-ecat-files:nil