2 $PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.74 2004/10/22 17:20:04 tgl Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-ALTERTABLE">
8 <refentrytitle id="sql-altertable-title">ALTER TABLE</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>ALTER TABLE</refname>
14 <refpurpose>change the definition of a table</refpurpose>
17 <indexterm zone="sql-altertable">
18 <primary>ALTER TABLE</primary>
23 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
24 <replaceable class="PARAMETER">action</replaceable> [, ... ]
25 ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
26 RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable class="PARAMETER">new_column</replaceable>
27 ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
28 RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
30 where <replaceable class="PARAMETER">action</replaceable> is one of:
32 ADD [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> <replaceable class="PARAMETER">type</replaceable> [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ]
33 DROP [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> [ RESTRICT | CASCADE ]
34 ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TYPE <replaceable class="PARAMETER">type</replaceable> [ USING <replaceable class="PARAMETER">expression</replaceable> ]
35 ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
36 ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> DROP DEFAULT
37 ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> { SET | DROP } NOT NULL
38 ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
39 ALTER [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
40 ADD <replaceable class="PARAMETER">table_constraint</replaceable>
41 DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
42 CLUSTER ON <replaceable class="PARAMETER">index_name</replaceable>
45 OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
46 SET TABLESPACE <replaceable class="PARAMETER">tablespace_name</replaceable>
51 <title>Description</title>
54 <command>ALTER TABLE</command> changes the definition of an existing table.
55 There are several subforms:
59 <term><literal>ADD COLUMN</literal></term>
62 This form adds a new column to the table using the same syntax as
63 <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
69 <term><literal>DROP COLUMN</literal></term>
72 This form drops a column from a table. Indexes and
73 table constraints involving the column will be automatically
74 dropped as well. You will need to say <literal>CASCADE</> if
75 anything outside the table depends on the column, for example,
76 foreign key references or views.
82 <term><literal>ALTER COLUMN TYPE</literal></term>
85 This form changes the type of a column of a table. Indexes and
86 simple table constraints involving the column will be automatically
87 converted to use the new column type by reparsing the originally
88 supplied expression. The optional <literal>USING</literal>
89 clause specifies how to compute the new column value from the old;
90 if omitted, the default conversion is the same as an assignment
91 cast from old data type to new. A <literal>USING</literal>
92 clause must be provided if there is no implicit or assignment
93 cast from old to new type.
99 <term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term>
102 These forms set or remove the default value for a column.
103 The default values only apply to subsequent <command>INSERT</command>
104 commands; they do not cause rows already in the table to change.
105 Defaults may also be created for views, in which case they are
106 inserted into <command>INSERT</> statements on the view before
107 the view's <literal>ON INSERT</literal> rule is applied.
113 <term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term>
116 These forms change whether a column is marked to allow null
117 values or to reject null values. You can only use <literal>SET
118 NOT NULL</> when the column contains no null values.
124 <term><literal>SET STATISTICS</literal></term>
128 sets the per-column statistics-gathering target for subsequent
129 <xref linkend="sql-analyze" endterm="sql-analyze-title"> operations.
130 The target can be set in the range 0 to 1000; alternatively, set it
131 to -1 to revert to using the system default statistics
132 target. For more information on the use of statistics by the
133 <productname>PostgreSQL</productname> query planner, refer to
134 <xref linkend="planner-stats">.
141 <primary>TOAST</primary>
142 <secondary>per-column storage settings</secondary>
145 <term><literal>SET STORAGE</literal></term>
148 This form sets the storage mode for a column. This controls whether this
149 column is held inline or in a supplementary table, and whether the data
150 should be compressed or not. <literal>PLAIN</literal> must be used
151 for fixed-length values such as <type>integer</type> and is
152 inline, uncompressed. <literal>MAIN</literal> is for inline,
153 compressible data. <literal>EXTERNAL</literal> is for external,
154 uncompressed data, and <literal>EXTENDED</literal> is for external,
155 compressed data. <literal>EXTENDED</literal> is the default for all
156 data types that support it. The use of <literal>EXTERNAL</literal> will, for example,
157 make substring operations on a <type>text</type> column faster, at the penalty of
158 increased storage space.
164 <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>
167 This form adds a new constraint to a table using the same syntax as
168 <xref linkend="SQL-CREATETABLE" endterm="SQL-CREATETABLE-TITLE">.
174 <term><literal>DROP CONSTRAINT</literal></term>
177 This form drops constraints on a table.
178 Currently, constraints on tables are not required to have unique
179 names, so there may be more than one constraint matching the specified
180 name. All matching constraints will be dropped.
186 <term><literal>CLUSTER</literal></term>
189 This form selects the default index for future
190 <xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
191 operations. It does not actually re-cluster the table.
197 <term><literal>SET WITHOUT CLUSTER</literal></term>
200 This form removes the most recently used
201 <xref linkend="SQL-CLUSTER" endterm="sql-cluster-title">
202 index specification from the table. This affects
203 future cluster operations that don't specify an index.
209 <term><literal>SET WITHOUT OIDS</literal></term>
212 This form removes the <literal>oid</literal> system column from the
213 table. This is exactly equivalent to
214 <literal>DROP COLUMN oid RESTRICT</literal>,
215 except that it will not complain if there is already no
216 <literal>oid</literal> column.
220 Note that there is no variant of <command>ALTER TABLE</command>
221 that allows OIDs to be restored to a table once they have been
228 <term><literal>OWNER</literal></term>
231 This form changes the owner of the table, index, sequence, or view to the
238 <term><literal>SET TABLESPACE</literal></term>
241 This form changes the table's tablespace to the specified tablespace and
242 moves the data file(s) associated with the table to the new tablespace.
243 Indexes on the table, if any, are not moved; but they can be moved
244 separately with additional <literal>SET TABLESPACE</literal> commands.
246 <xref linkend="SQL-CREATETABLESPACE" endterm="sql-createtablespace-title">.
252 <term><literal>RENAME</literal></term>
255 The <literal>RENAME</literal> forms change the name of a table
256 (or an index, sequence, or view) or the name of an individual column in
257 a table. There is no effect on the stored data.
266 All the actions except <literal>RENAME</literal> can be combined into
267 a list of multiple alterations to apply in parallel. For example, it
268 is possible to add several columns and/or alter the type of several
269 columns in a single command. This is particularly useful with large
270 tables, since only one pass over the table need be made.
274 You must own the table to use <command>ALTER TABLE</>; except for
275 <command>ALTER TABLE OWNER</>, which may only be executed by a superuser.
280 <title>Parameters</title>
285 <term><replaceable class="PARAMETER">name</replaceable></term>
288 The name (possibly schema-qualified) of an existing table to
289 alter. If <literal>ONLY</> is specified, only that table is
290 altered. If <literal>ONLY</> is not specified, the table and all
291 its descendant tables (if any) are updated. <literal>*</> can be
292 appended to the table name to indicate that descendant tables are
293 to be altered, but in the current version, this is the default
294 behavior. (In releases before 7.1, <literal>ONLY</> was the
295 default behavior. The default can be altered by changing the
296 configuration parameter <xref linkend="guc-sql-inheritance">.)
302 <term><replaceable class="PARAMETER">column</replaceable></term>
305 Name of a new or existing column.
311 <term><replaceable class="PARAMETER">new_column</replaceable></term>
314 New name for an existing column.
320 <term><replaceable class="PARAMETER">new_name</replaceable></term>
323 New name for the table.
329 <term><replaceable class="PARAMETER">type</replaceable></term>
332 Data type of the new column, or new data type for an existing
339 <term><replaceable class="PARAMETER">table_constraint</replaceable></term>
342 New table constraint for the table.
348 <term><replaceable class="PARAMETER">constraint_name</replaceable></term>
351 Name of an existing constraint to drop.
357 <term><literal>CASCADE</literal></term>
360 Automatically drop objects that depend on the dropped column
361 or constraint (for example, views referencing the column).
367 <term><literal>RESTRICT</literal></term>
370 Refuse to drop the column or constraint if there are any dependent
371 objects. This is the default behavior.
377 <term><replaceable class="PARAMETER">index_name</replaceable></term>
380 The index name on which the table should be marked for clustering.
386 <term><replaceable class="PARAMETER">new_owner</replaceable></term>
389 The user name of the new owner of the table.
395 <term><replaceable class="PARAMETER">tablespace_name</replaceable></term>
398 The tablespace name to which the table will be moved.
410 The key word <literal>COLUMN</literal> is noise and can be omitted.
414 When a column is added with <literal>ADD COLUMN</literal>, all existing
415 rows in the table are initialized with the column's default value
416 (NULL if no <literal>DEFAULT</> clause is specified).
420 Adding a column with a non-null default or changing the type of an
421 existing column will require the entire table to be rewritten. This
422 may take a significant amount of time for a large table; and it will
423 temporarily require double the disk space.
427 Adding a <literal>CHECK</> or <literal>NOT NULL</> constraint requires
428 scanning the table to verify that existing rows meet the constraint.
432 The main reason for providing the option to specify multiple changes
433 in a single <command>ALTER TABLE</> is that multiple table scans or
434 rewrites can thereby be combined into a single pass over the table.
438 The <literal>DROP COLUMN</literal> form does not physically remove
439 the column, but simply makes it invisible to SQL operations. Subsequent
440 insert and update operations in the table will store a null value for the
441 column. Thus, dropping a column is quick but it will not immediately
442 reduce the on-disk size of your table, as the space occupied
443 by the dropped column is not reclaimed. The space will be
444 reclaimed over time as existing rows are updated.
448 The fact that <literal>ALTER TYPE</> requires rewriting the whole table
449 is sometimes an advantage, because the rewriting process eliminates
450 any dead space in the table. For example, to reclaim the space occupied
451 by a dropped column immediately, the fastest way is
453 ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
455 where <literal>anycol</> is any remaining table column and
456 <literal>anytype</> is the same type that column already has.
457 This results in no semantically-visible change in the table,
458 but the command forces rewriting, which gets rid of no-longer-useful
463 The <literal>USING</literal> option of <literal>ALTER TYPE</> can actually
464 specify any expression involving the old values of the row; that is, it
465 can refer to other columns as well as the one being converted. This allows
466 very general conversions to be done with the <literal>ALTER TYPE</>
467 syntax. Because of this flexibility, the <literal>USING</literal>
468 expression is not applied to the column's default value (if any); the
469 result might not be a constant expression as required for a default.
470 This means that when there is no implicit or assignment cast from old to
471 new type, <literal>ALTER TYPE</> may fail to convert the default even
472 though a <literal>USING</literal> clause is supplied. In such cases,
473 drop the default with <literal>DROP DEFAULT</>, perform the <literal>ALTER
474 TYPE</>, and then use <literal>SET DEFAULT</> to add a suitable new
479 If a table has any descendant tables, it is not permitted to add,
480 rename, or change the type of a column in the parent table without doing
481 the same to the descendants. That is, <command>ALTER TABLE ONLY</command>
482 will be rejected. This ensures that the descendants always have
483 columns matching the parent.
487 A recursive <literal>DROP COLUMN</literal> operation will remove a
488 descendant table's column only if the descendant does not inherit
489 that column from any other parents and never had an independent
490 definition of the column. A nonrecursive <literal>DROP
491 COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP
492 COLUMN</command>) never removes any descendant columns, but
493 instead marks them as independently defined rather than inherited.
497 Changing any part of a system catalog table is not permitted.
501 Refer to <xref linkend="sql-createtable"
502 endterm="sql-createtable-title"> for a further description of valid
503 parameters. <xref linkend="ddl"> has further information on
509 <title>Examples</title>
512 To add a column of type <type>varchar</type> to a table:
514 ALTER TABLE distributors ADD COLUMN address varchar(30);
519 To drop a column from a table:
521 ALTER TABLE distributors DROP COLUMN address RESTRICT;
526 To change the types of two existing columns in one operation:
528 ALTER TABLE distributors
529 ALTER COLUMN address TYPE varchar(80),
530 ALTER COLUMN name TYPE varchar(100);
535 To rename an existing column:
537 ALTER TABLE distributors RENAME COLUMN address TO city;
542 To rename an existing table:
544 ALTER TABLE distributors RENAME TO suppliers;
549 To add a not-null constraint to a column:
551 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
553 To remove a not-null constraint from a column:
555 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
560 To add a check constraint to a table:
562 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
567 To remove a check constraint from a table and all its children:
569 ALTER TABLE distributors DROP CONSTRAINT zipchk;
574 To add a foreign key constraint to a table:
576 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
581 To add a (multicolumn) unique constraint to a table:
583 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
588 To add an automatically named primary key constraint to a table, noting
589 that a table can only ever have one primary key:
591 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
596 To move a table to a different tablespace:
598 ALTER TABLE distributors SET TABLESPACE fasttablespace;
605 <title>Compatibility</title>
608 The <literal>ADD</literal>, <literal>DROP</>, and <literal>SET DEFAULT</>
609 forms conform with the SQL standard. The other forms are
610 <productname>PostgreSQL</productname> extensions of the SQL standard.
611 Also, the ability to specify more than one manipulation in a single
612 <command>ALTER TABLE</> command is an extension.
616 <command>ALTER TABLE DROP COLUMN</> can be used to drop the only
617 column of a table, leaving a zero-column table. This is an
618 extension of SQL, which disallows zero-column tables.
623 <!-- Keep this comment at the end of the file
628 sgml-minimize-attributes:nil
629 sgml-always-quote-attributes:t
632 sgml-parent-document:nil
633 sgml-default-dtd-file:"../reference.ced"
634 sgml-exposed-tags:nil
635 sgml-local-catalogs:"/usr/lib/sgml/catalog"
636 sgml-local-ecat-files:nil