1ALTER TABLE() SQL Commands ALTER TABLE()
2
6 ALTER TABLE - change the definition of a table
7
10 ALTER TABLE [ ONLY ] name [ * ]
11 action [, ... ]
12 ALTER TABLE [ ONLY ] name [ * ]
13 RENAME [ COLUMN ] column TO new_column
14 ALTER TABLE name
15 RENAME TO new_name
16 ALTER TABLE name
17 SET SCHEMA new_schema
18 where action is one of:
20 ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
22 DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
23 ALTER [ COLUMN ] column TYPE type [ USING expression ]
24 ALTER [ COLUMN ] column SET DEFAULT expression
25 ALTER [ COLUMN ] column DROP DEFAULT
26 ALTER [ COLUMN ] column { SET | DROP } NOT NULL
27 ALTER [ COLUMN ] column SET STATISTICS integer
28 ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
29 ADD table_constraint
30 DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
31 DISABLE TRIGGER [ trigger_name | ALL | USER ]
32 ENABLE TRIGGER [ trigger_name | ALL | USER ]
33 CLUSTER ON index_name
34 SET WITHOUT CLUSTER
35 SET WITHOUT OIDS
36 SET ( storage_parameter = value [, ... ] )
37 RESET ( storage_parameter [, ... ] )
38 INHERIT parent_table
39 NO INHERIT parent_table
40 OWNER TO new_owner
41 SET TABLESPACE new_tablespace
42
45 ALTER TABLE changes the definition of an existing table. There are
46 several subforms:
47 ADD COLUMN
49 This form adds a new column to the table, using the same syntax
50 as CREATE TABLE [create_table(7)].
51 DROP COLUMN
53 This form drops a column from a table. Indexes and table con‐
54 straints involving the column will be automatically dropped as
55 well. You will need to say CASCADE if anything outside the table
56 depends on the column, for example, foreign key references or
57 views.
58 ALTER COLUMN TYPE
60 This form changes the type of a column of a table. Indexes and
61 simple table constraints involving the column will be automati‐
62 cally converted to use the new column type by reparsing the
63 originally supplied expression. The optional USING clause speci‐
64 fies how to compute the new column value from the old; if omit‐
65 ted, the default conversion is the same as an assignment cast
66 from old data type to new. A USING clause must be provided if
67 there is no implicit or assignment cast from old to new type.
68 SET/DROP DEFAULT
70 These forms set or remove the default value for a column. The
71 default values only apply to subsequent INSERT commands; they do
72 not cause rows already in the table to change. Defaults may
73 also be created for views, in which case they are inserted into
74 INSERT statements on the view before the view's ON INSERT rule
75 is applied.
76 SET/DROP NOT NULL
78 These forms change whether a column is marked to allow null val‐
79 ues or to reject null values. You can only use SET NOT NULL when
80 the column contains no null values.
81 SET STATISTICS
83 This form sets the per-column statistics-gathering target for
84 subsequent ANALYZE [analyze(7)] operations. The target can be
85 set in the range 0 to 1000; alternatively, set it to -1 to
86 revert to using the system default statistics target
87 (default_statistics_target). For more information on the use of
88 statistics by the PostgreSQL query planner, refer to in the doc‐
89 umentation.
90 SET STORAGE
93 This form sets the storage mode for a column. This controls
94 whether this column is held inline or in a supplementary table,
95 and whether the data should be compressed or not. PLAIN must be
96 used for fixed-length values such as integer and is inline,
97 uncompressed. MAIN is for inline, compressible data. EXTERNAL is
98 for external, uncompressed data, and EXTENDED is for external,
99 compressed data. EXTENDED is the default for most data types
100 that support non-PLAIN storage. Use of EXTERNAL will make sub‐
101 string operations on text and bytea columns faster, at the
102 penalty of increased storage space. Note that SET STORAGE
103 doesn't itself change anything in the table, it just sets the
104 strategy to be pursued during future table updates. See in the
105 documentation for more information.
106 ADD table_constraint
108 This form adds a new constraint to a table using the same syntax
109 as CREATE TABLE [create_table(7)].
110 DROP CONSTRAINT
112 This form drops the specified constraint on a table.
113 DISABLE/ENABLE TRIGGER
115 These forms disable or enable trigger(s) belonging to the table.
116 A disabled trigger is still known to the system, but is not exe‐
117 cuted when its triggering event occurs. For a deferred trigger,
118 the enable status is checked when the event occurs, not when the
119 trigger function is actually executed. One may disable or enable
120 a single trigger specified by name, or all triggers on the ta‐
121 ble, or only user triggers (this option excludes triggers that
122 are used to implement foreign key constraints). Disabling or
123 enabling constraint triggers requires superuser privileges; it
124 should be done with caution since of course the integrity of the
125 constraint cannot be guaranteed if the triggers are not exe‐
126 cuted.
127 CLUSTER
129 This form selects the default index for future CLUSTER [clus‐
130 ter(7)] operations. It does not actually re-cluster the table.
131 SET WITHOUT CLUSTER
133 This form removes the most recently used CLUSTER [cluster(7)]
134 index specification from the table. This affects future cluster
135 operations that don't specify an index.
136 SET WITHOUT OIDS
138 This form removes the oid system column from the table. This is
139 exactly equivalent to DROP COLUMN oid RESTRICT, except that it
140 will not complain if there is already no oid column.
141 Note that there is no variant of ALTER TABLE that allows OIDs to
143 be restored to a table once they have been removed.
144 SET ( storage_parameter = value [, ... ] )
146 This form changes one or more storage parameters for the table.
147 See CREATE TABLE [create_table(7)] for details on the available
148 parameters. Note that the table contents will not be modified
149 immediately by this command; depending on the parameter you may
150 need to rewrite the table to get the desired effects. That can
151 be done with CLUSTER [cluster(7)] or one of the forms of ALTER
152 TABLE that forces a table rewrite.
153 Note: While CREATE TABLE allows OIDS to be specified in the WITH
155 (storage_parameter) syntax, ALTER TABLE does not treat OIDS as a
156 storage parameter.
157 RESET ( storage_parameter [, ... ] )
160 This form resets one or more storage parameters to their
161 defaults. As with SET, a table rewrite may be needed to update
162 the table entirely.
163 INHERIT parent_table
165 This form adds the target table as a new child of the specified
166 parent table. Subsequently, queries against the parent will
167 include records of the target table. To be added as a child, the
168 target table must already contain all the same columns as the
169 parent (it could have additional columns, too). The columns must
170 have matching data types, and if they have NOT NULL constraints
171 in the parent then they must also have NOT NULL constraints in
172 the child.
173 There must also be matching child-table constraints for all
175 CHECK constraints of the parent. Currently UNIQUE, PRIMARY KEY,
176 and FOREIGN KEY constraints are not considered, but this may
177 change in the future.
178 NO INHERIT parent_table
180 This form removes the target table from the list of children of
181 the specified parent table. Queries against the parent table
182 will no longer include records drawn from the target table.
183 OWNER This form changes the owner of the table, sequence, or view to
185 the specified user.
186 SET TABLESPACE
188 This form changes the table's tablespace to the specified
189 tablespace and moves the data file(s) associated with the table
190 to the new tablespace. Indexes on the table, if any, are not
191 moved; but they can be moved separately with additional SET
192 TABLESPACE commands. See also CREATE TABLESPACE [cre‐
193 ate_tablespace(7)].
194 RENAME The RENAME forms change the name of a table (or an index,
196 sequence, or view) or the name of an individual column in a ta‐
197 ble. There is no effect on the stored data.
198 SET SCHEMA
200 This form moves the table into another schema. Associated
201 indexes, constraints, and sequences owned by table columns are
202 moved as well.
203 All the actions except RENAME and SET SCHEMA can be combined into a
205 list of multiple alterations to apply in parallel. For example, it is
206 possible to add several columns and/or alter the type of several col‐
207 umns in a single command. This is particularly useful with large
208 tables, since only one pass over the table need be made.
209 You must own the table to use ALTER TABLE. To change the schema of a
211 table, you must also have CREATE privilege on the new schema. To add
212 the table as a new child of a parent table, you must own the parent ta‐
213 ble as well. To alter the owner, you must also be a direct or indirect
214 member of the new owning role, and that role must have CREATE privilege
215 on the table's schema. (These restrictions enforce that altering the
216 owner doesn't do anything you couldn't do by dropping and recreating
217 the table. However, a superuser can alter ownership of any table any‐
218 way.)
219
221 name The name (possibly schema-qualified) of an existing table to
222 alter. If ONLY is specified, only that table is altered. If ONLY
223 is not specified, the table and all its descendant tables (if
224 any) are updated. * can be appended to the table name to indi‐
225 cate that descendant tables are to be altered, but in the cur‐
226 rent version, this is the default behavior. (In releases before
227 7.1, ONLY was the default behavior. The default can be altered
228 by changing the configuration parameter sql_inheritance.)
229 column Name of a new or existing column.
231 new_column
233 New name for an existing column.
234 new_name
236 New name for the table.
237 type Data type of the new column, or new data type for an existing
239 column.
240 table_constraint
242 New table constraint for the table.
243 constraint_name
245 Name of an existing constraint to drop.
246 CASCADE
248 Automatically drop objects that depend on the dropped column or
249 constraint (for example, views referencing the column).
250 RESTRICT
252 Refuse to drop the column or constraint if there are any depen‐
253 dent objects. This is the default behavior.
254 trigger_name
256 Name of a single trigger to disable or enable.
257 ALL Disable or enable all triggers belonging to the table. (This
259 requires superuser privilege if any of the triggers are for for‐
260 eign key constraints.)
261 USER Disable or enable all triggers belonging to the table except for
263 foreign key constraint triggers.
264 index_name
266 The index name on which the table should be marked for cluster‐
267 ing.
268 storage_parameter
270 The name of a table storage parameter.
271 value The new value for a table storage parameter. This might be a
273 number or a word depending on the parameter.
274 parent_table
276 A parent table to associate or de-associate with this table.
277 new_owner
279 The user name of the new owner of the table.
280 new_tablespace
282 The name of the tablespace to which the table will be moved.
283 new_schema
285 The name of the schema to which the table will be moved.
286
288 The key word COLUMN is noise and can be omitted.
289 When a column is added with ADD COLUMN, all existing rows in the table
291 are initialized with the column's default value (NULL if no DEFAULT
292 clause is specified).
293 Adding a column with a non-null default or changing the type of an
295 existing column will require the entire table to be rewritten. This may
296 take a significant amount of time for a large table; and it will tempo‐
297 rarily require double the disk space.
298 Adding a CHECK or NOT NULL constraint requires scanning the table to
300 verify that existing rows meet the constraint.
301 The main reason for providing the option to specify multiple changes in
303 a single ALTER TABLE is that multiple table scans or rewrites can
304 thereby be combined into a single pass over the table.
305 The DROP COLUMN form does not physically remove the column, but simply
307 makes it invisible to SQL operations. Subsequent insert and update
308 operations in the table will store a null value for the column. Thus,
309 dropping a column is quick but it will not immediately reduce the on-
310 disk size of your table, as the space occupied by the dropped column is
311 not reclaimed. The space will be reclaimed over time as existing rows
312 are updated.
313 The fact that ALTER TYPE requires rewriting the whole table is some‐
315 times an advantage, because the rewriting process eliminates any dead
316 space in the table. For example, to reclaim the space occupied by a
317 dropped column immediately, the fastest way is
318 ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
320 where anycol is any remaining table column and anytype is the same type
322 that column already has. This results in no semantically-visible
323 change in the table, but the command forces rewriting, which gets rid
324 of no-longer-useful data.
325 The USING option of ALTER TYPE can actually specify any expression
327 involving the old values of the row; that is, it can refer to other
328 columns as well as the one being converted. This allows very general
329 conversions to be done with the ALTER TYPE syntax. Because of this
330 flexibility, the USING expression is not applied to the column's
331 default value (if any); the result might not be a constant expression
332 as required for a default. This means that when there is no implicit
333 or assignment cast from old to new type, ALTER TYPE may fail to convert
334 the default even though a USING clause is supplied. In such cases, drop
335 the default with DROP DEFAULT, perform the ALTER TYPE, and then use SET
336 DEFAULT to add a suitable new default. Similar considerations apply to
337 indexes and constraints involving the column.
338 If a table has any descendant tables, it is not permitted to add,
340 rename, or change the type of a column in the parent table without
341 doing the same to the descendants. That is, ALTER TABLE ONLY will be
342 rejected. This ensures that the descendants always have columns match‐
343 ing the parent.
344 A recursive DROP COLUMN operation will remove a descendant table's col‐
346 umn only if the descendant does not inherit that column from any other
347 parents and never had an independent definition of the column. A nonre‐
348 cursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never
349 removes any descendant columns, but instead marks them as independently
350 defined rather than inherited.
351 The TRIGGER, CLUSTER, OWNER, and TABLESPACE actions never recurse to
353 descendant tables; that is, they always act as though ONLY were speci‐
354 fied. Adding a constraint can recurse only for CHECK constraints.
355 Changing any part of a system catalog table is not permitted.
357 Refer to CREATE TABLE [create_table(7)] for a further description of
359 valid parameters. in the documentation has further information on
360 inheritance.
361
363 To add a column of type varchar to a table:
364 ALTER TABLE distributors ADD COLUMN address varchar(30);
366 To drop a column from a table:
369 ALTER TABLE distributors DROP COLUMN address RESTRICT;
371 To change the types of two existing columns in one operation:
374 ALTER TABLE distributors
376 ALTER COLUMN address TYPE varchar(80),
377 ALTER COLUMN name TYPE varchar(100);
378 To change an integer column containing UNIX timestamps to timestamp
381 with time zone via a USING clause:
382 ALTER TABLE foo
384 ALTER COLUMN foo_timestamp TYPE timestamp with time zone
385 USING
386 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
387 The same, when the column has a default expression that won't automati‐
390 cally cast to the new data type:
391 ALTER TABLE foo
393 ALTER COLUMN foo_timestamp DROP DEFAULT,
394 ALTER COLUMN foo_timestamp TYPE timestamp with time zone
395 USING
396 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
397 ALTER COLUMN foo_timestamp SET DEFAULT now();
398 To rename an existing column:
401 ALTER TABLE distributors RENAME COLUMN address TO city;
403 To rename an existing table:
406 ALTER TABLE distributors RENAME TO suppliers;
408 To add a not-null constraint to a column:
411 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
413 To remove a not-null constraint from a column:
415 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
417 To add a check constraint to a table:
420 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
422 To remove a check constraint from a table and all its children:
425 ALTER TABLE distributors DROP CONSTRAINT zipchk;
427 To add a foreign key constraint to a table:
430 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
432 To add a (multicolumn) unique constraint to a table:
435 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
437 To add an automatically named primary key constraint to a table, noting
440 that a table can only ever have one primary key:
441 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
443 To move a table to a different tablespace:
446 ALTER TABLE distributors SET TABLESPACE fasttablespace;
448 To move a table to a different schema:
451 ALTER TABLE myschema.distributors SET SCHEMA yourschema;
453
456 The ADD, DROP, and SET DEFAULT forms conform with the SQL standard. The
457 other forms are PostgreSQL extensions of the SQL standard. Also, the
458 ability to specify more than one manipulation in a single ALTER TABLE
459 command is an extension.
460 ALTER TABLE DROP COLUMN can be used to drop the only column of a table,
462 leaving a zero-column table. This is an extension of SQL, which disal‐
463 lows zero-column tables.
464SQL - Language Statements 2008-06-08 ALTER TABLE()