1 .\\" auto-generated by docbook2man-spec $Revision: 1.25 $
2 .TH "ALTER TABLE" "7" "2003-11-02" "SQL - Language Statements" "SQL Commands"
4 ALTER TABLE \- change the definition of a table
9 ALTER TABLE [ ONLY ] \fIname\fR [ * ]
10 ADD [ COLUMN ] \fIcolumn\fR \fItype\fR [ \fIcolumn_constraint\fR [ ... ] ]
11 ALTER TABLE [ ONLY ] \fIname\fR [ * ]
12 DROP [ COLUMN ] \fIcolumn\fR [ RESTRICT | CASCADE ]
13 ALTER TABLE [ ONLY ] \fIname\fR [ * ]
14 ALTER [ COLUMN ] \fIcolumn\fR { SET DEFAULT \fIexpression\fR | DROP DEFAULT }
15 ALTER TABLE [ ONLY ] \fIname\fR [ * ]
16 ALTER [ COLUMN ] \fIcolumn\fR { SET | DROP } NOT NULL
17 ALTER TABLE [ ONLY ] \fIname\fR [ * ]
18 ALTER [ COLUMN ] \fIcolumn\fR SET STATISTICS \fIinteger\fR
19 ALTER TABLE [ ONLY ] \fIname\fR [ * ]
20 ALTER [ COLUMN ] \fIcolumn\fR SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
21 ALTER TABLE [ ONLY ] \fIname\fR [ * ]
23 ALTER TABLE [ ONLY ] \fIname\fR [ * ]
24 RENAME [ COLUMN ] \fIcolumn\fR TO \fInew_column\fR
25 ALTER TABLE \fIname\fR
26 RENAME TO \fInew_name\fR
27 ALTER TABLE [ ONLY ] \fIname\fR [ * ]
28 ADD \fItable_constraint\fR
29 ALTER TABLE [ ONLY ] \fIname\fR [ * ]
30 DROP CONSTRAINT \fIconstraint_name\fR [ RESTRICT | CASCADE ]
31 ALTER TABLE \fIname\fR
32 OWNER TO \fInew_owner\fR
33 ALTER TABLE \fIname\fR
34 CLUSTER ON \fIindex_name\fR
39 \fBALTER TABLE\fR changes the definition of an existing table.
40 There are several subforms:
43 This form adds a new column to the table using the same syntax as
44 CREATE TABLE [\fBcreate_table\fR(7)].
47 This form drops a column from a table. Indexes and
48 table constraints involving the column will be automatically
49 dropped as well. You will need to say CASCADE if
50 anything outside the table depends on the column, for example,
51 foreign key references or views.
53 \fBSET/DROP DEFAULT\fR
54 These forms set or remove the default value for a column.
55 The default values only apply to subsequent \fBINSERT\fR
56 commands; they do not cause rows already in the table to change.
57 Defaults may also be created for views, in which case they are
58 inserted into \fBINSERT\fR statements on the view before
59 the view's ON INSERT rule is applied.
61 \fBSET/DROP NOT NULL\fR
62 These forms change whether a column is marked to allow null
63 values or to reject null values. You can only use SET
64 NOT NULL when the column contains no null values.
68 sets the per-column statistics-gathering target for subsequent
69 ANALYZE [\fBanalyze\fR(7)] operations.
70 The target can be set in the range 0 to 1000; alternatively, set it
71 to -1 to revert to using the system default statistics target.
74 This form sets the storage mode for a column. This controls whether this
75 column is held inline or in a supplementary table, and whether the data
76 should be compressed or not. PLAIN must be used
77 for fixed-length values such as \fBinteger\fR and is
78 inline, uncompressed. MAIN is for inline,
79 compressible data. EXTERNAL is for external,
80 uncompressed data, and EXTENDED is for external,
81 compressed data. EXTENDED is the default for all
82 data types that support it. The use of EXTERNAL will, for example,
83 make substring operations on a \fBtext\fR column faster, at the penalty of
84 increased storage space.
86 \fBSET WITHOUT OIDS\fR
87 This form removes the oid column from the
88 table. Removing OIDs from a table does not occur immediately.
89 The space that the OID uses will be reclaimed when the row is
90 updated. Without updating the row, both the space and the value
91 of the OID are kept indefinitely. This is semantically similar
92 to the DROP COLUMN process.
95 The RENAME forms change the name of a table
96 (or an index, sequence, or view) or the name of an individual column in
97 a table. There is no effect on the stored data.
99 \fBADD \fItable_constraint\fB\fR
100 This form adds a new constraint to a table using the same syntax as
101 CREATE TABLE [\fBcreate_table\fR(7)].
103 \fBDROP CONSTRAINT\fR
104 This form drops constraints on a table.
105 Currently, constraints on tables are not required to have unique
106 names, so there may be more than one constraint matching the specified
107 name. All such constraints will be dropped.
110 This form changes the owner of the table, index, sequence, or view to the
114 This form marks a table for future CLUSTER [\fBcluster\fR(7)]
118 You must own the table to use \fBALTER TABLE\fR; except for
119 \fBALTER TABLE OWNER\fR, which may only be executed by a superuser.
123 The name (possibly schema-qualified) of an existing table to
124 alter. If ONLY is specified, only that table is
125 altered. If ONLY is not specified, the table and all
126 its descendant tables (if any) are updated. * can be
127 appended to the table name to indicate that descendant tables are
128 to be altered, but in the current version, this is the default
129 behavior. (In releases before 7.1, ONLY was the
130 default behavior. The default can be altered by changing the
131 configuration parameter sql_inheritance.)
134 Name of a new or existing column.
137 Data type of the new column.
139 \fB\fInew_column\fB\fR
140 New name for an existing column.
143 New name for the table.
145 \fB\fItable_constraint\fB\fR
146 New table constraint for the table.
148 \fB\fIconstraint_name\fB\fR
149 Name of an existing constraint to drop.
151 \fB\fInew_owner\fB\fR
152 The user name of the new owner of the table.
154 \fB\fIindex_name\fB\fR
155 The index name on which the table should be marked for clustering.
158 Automatically drop objects that depend on the dropped column
159 or constraint (for example, views referencing the column).
162 Refuse to drop the column or constraint if there are any dependent
163 objects. This is the default behavior.
166 The key word COLUMN is noise and can be omitted.
168 In the current implementation of ADD COLUMN,
169 default and NOT NULL clauses for the new column are not supported.
170 The new column always comes into being with all values null.
171 You can use the SET DEFAULT form
172 of \fBALTER TABLE\fR to set the default afterward.
173 (You may also want to update the already existing rows to the
174 new default value, using
175 UPDATE [\fBupdate\fR(7)].)
176 If you want to mark the column non-null, use the SET NOT NULL
177 form after you've entered non-null values for the column in all rows.
179 The DROP COLUMN form does not physically remove
180 the column, but simply makes it invisible to SQL operations. Subsequent
181 insert and update operations in the table will store a null value for the column.
182 Thus, dropping a column is quick but it will not immediately reduce the
183 on-disk size of your table, as the space occupied
184 by the dropped column is not reclaimed. The space will be
185 reclaimed over time as existing rows are updated.
186 To reclaim the space at once, do a dummy \fBUPDATE\fR of all rows
187 and then vacuum, as in:
190 UPDATE table SET col = col;
195 If a table has any descendant tables, it is not permitted to add
196 or rename a column in the parent table without doing the same to
197 the descendants. That is, \fBALTER TABLE ONLY\fR
198 will be rejected. This ensures that the descendants always have
199 columns matching the parent.
201 A recursive DROP COLUMN operation will remove a
202 descendant table's column only if the descendant does not inherit
203 that column from any other parents and never had an independent
204 definition of the column. A nonrecursive DROP
205 COLUMN (i.e., \fBALTER TABLE ONLY ... DROP
206 COLUMN\fR) never removes any descendant columns, but
207 instead marks them as independently defined rather than inherited.
209 Changing any part of a system catalog table is not permitted.
211 Refer to \fBCREATE TABLE\fR for a further description
212 of valid parameters. The chapter called ``Data Definition'' in the documentation has further information on
216 To add a column of type \fBvarchar\fR to a table:
219 ALTER TABLE distributors ADD COLUMN address varchar(30);
223 To drop a column from a table:
226 ALTER TABLE distributors DROP COLUMN address RESTRICT;
230 To rename an existing column:
233 ALTER TABLE distributors RENAME COLUMN address TO city;
237 To rename an existing table:
240 ALTER TABLE distributors RENAME TO suppliers;
244 To add a not-null constraint to a column:
247 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
250 To remove a not-null constraint from a column:
253 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
257 To add a check constraint to a table:
260 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
264 To remove a check constraint from a table and all its children:
267 ALTER TABLE distributors DROP CONSTRAINT zipchk;
271 To add a foreign key constraint to a table:
274 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
278 To add a (multicolumn) unique constraint to a table:
281 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
285 To add an automatically named primary key constraint to a table, noting
286 that a table can only ever have one primary key:
289 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
294 The ADD COLUMN form conforms with the SQL
295 standard, with the exception that it does not support defaults and
296 not-null constraints, as explained above. The ALTER
297 COLUMN form is in full conformance.
299 The clauses to rename tables, columns, indexes, views, and sequences are
300 PostgreSQL extensions of the SQL standard.
302 \fBALTER TABLE DROP COLUMN\fR can be used to drop the only
303 column of a table, leaving a zero-column table. This is an
304 extension of SQL, which disallows zero-column tables.
OSDN Git Service
