1ALTER TABLE(7) SQL Commands ALTER TABLE(7)
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 [ SET DATA ] 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 ENABLE REPLICA TRIGGER trigger_name
34 ENABLE ALWAYS TRIGGER trigger_name
35 DISABLE RULE rewrite_rule_name
36 ENABLE RULE rewrite_rule_name
37 ENABLE REPLICA RULE rewrite_rule_name
38 ENABLE ALWAYS RULE rewrite_rule_name
39 CLUSTER ON index_name
40 SET WITHOUT CLUSTER
41 SET WITH OIDS
42 SET WITHOUT OIDS
43 SET ( storage_parameter = value [, ... ] )
44 RESET ( storage_parameter [, ... ] )
45 INHERIT parent_table
46 NO INHERIT parent_table
47 OWNER TO new_owner
48 SET TABLESPACE new_tablespace
49
52 ALTER TABLE changes the definition of an existing table. There are
53 several subforms:
54 ADD COLUMN
56 This form adds a new column to the table, using the same syntax
57 as CREATE TABLE [create_table(7)].
58 DROP COLUMN
60 This form drops a column from a table. Indexes and table con‐
61 straints involving the column will be automatically dropped as
62 well. You will need to say CASCADE if anything outside the table
63 depends on the column, for example, foreign key references or
64 views.
65 SET DATA TYPE
67 This form changes the type of a column of a table. Indexes and
68 simple table constraints involving the column will be automati‐
69 cally converted to use the new column type by reparsing the
70 originally supplied expression. The optional USING clause speci‐
71 fies how to compute the new column value from the old; if omit‐
72 ted, the default conversion is the same as an assignment cast
73 from old data type to new. A USING clause must be provided if
74 there is no implicit or assignment cast from old to new type.
75 SET/DROP DEFAULT
77 These forms set or remove the default value for a column. The
78 default values only apply to subsequent INSERT commands; they do
79 not cause rows already in the table to change. Defaults can
80 also be created for views, in which case they are inserted into
81 INSERT statements on the view before the view's ON INSERT rule
82 is applied.
83 SET/DROP NOT NULL
85 These forms change whether a column is marked to allow null val‐
86 ues or to reject null values. You can only use SET NOT NULL when
87 the column contains no null values.
88 SET STATISTICS
90 This form sets the per-column statistics-gathering target for
91 subsequent ANALYZE [analyze(7)] operations. The target can be
92 set in the range 0 to 10000; alternatively, set it to -1 to
93 revert to using the system default statistics target
94 (default_statistics_target). For more information on the use of
95 statistics by the PostgreSQL query planner, refer to in the doc‐
96 umentation.
97 SET STORAGE
100 This form sets the storage mode for a column. This controls
101 whether this column is held inline or in a secondary TOAST ta‐
102 ble, and whether the data should be compressed or not. PLAIN
103 must be used for fixed-length values such as integer and is
104 inline, uncompressed. MAIN is for inline, compressible data.
105 EXTERNAL is for external, uncompressed data, and EXTENDED is for
106 external, compressed data. EXTENDED is the default for most data
107 types that support non-PLAIN storage. Use of EXTERNAL will make
108 substring operations on very large text and bytea values run
109 faster, at the penalty of increased storage space. Note that SET
110 STORAGE doesn't itself change anything in the table, it just
111 sets the strategy to be pursued during future table updates.
112 See in the documentation for more information.
113 ADD table_constraint
115 This form adds a new constraint to a table using the same syntax
116 as CREATE TABLE [create_table(7)].
117 DROP CONSTRAINT
119 This form drops the specified constraint on a table.
120 DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
122 These forms configure the firing of trigger(s) belonging to the
123 table. A disabled trigger is still known to the system, but is
124 not executed when its triggering event occurs. For a deferred
125 trigger, the enable status is checked when the event occurs, not
126 when the trigger function is actually executed. One can disable
127 or enable a single trigger specified by name, or all triggers on
128 the table, or only user triggers (this option excludes triggers
129 that are used to implement foreign key constraints). Disabling
130 or enabling constraint triggers requires superuser privileges;
131 it should be done with caution since of course the integrity of
132 the constraint cannot be guaranteed if the triggers are not exe‐
133 cuted. The trigger firing mechanism is also affected by the
134 configuration variable session_replication_role. Simply enabled
135 triggers will fire when the replication role is ``origin'' (the
136 default) or ``local''. Triggers configured as ENABLE REPLICA
137 will only fire if the session is in ``replica'' mode, and trig‐
138 gers configured as ENABLE ALWAYS will fire regardless of the
139 current replication mode.
140 DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
142 These forms configure the firing of rewrite rules belonging to
143 the table. A disabled rule is still known to the system, but is
144 not applied during query rewriting. The semantics are as for
145 disabled/enabled triggers. This configuration is ignored for ON
146 SELECT rules, which are always applied in order to keep views
147 working even if the current session is in a non-default replica‐
148 tion role.
149 CLUSTER
151 This form selects the default index for future CLUSTER [clus‐
152 ter(7)] operations. It does not actually re-cluster the table.
153 SET WITHOUT CLUSTER
155 This form removes the most recently used CLUSTER [cluster(7)]
156 index specification from the table. This affects future cluster
157 operations that don't specify an index.
158 SET WITH OIDS
160 This form adds an oid system column to the table (see in the
161 documentation). It does nothing if the table already has OIDs.
162 Note that this is not equivalent to ADD COLUMN oid oid; that
164 would add a normal column that happened to be named oid, not a
165 system column.
166 SET WITHOUT OIDS
168 This form removes the oid system column from the table. This is
169 exactly equivalent to DROP COLUMN oid RESTRICT, except that it
170 will not complain if there is already no oid column.
171 SET ( storage_parameter = value [, ... ] )
173 This form changes one or more storage parameters for the table.
174 See Storage Parameters [create_table(7)] for details on the
175 available parameters. Note that the table contents will not be
176 modified immediately by this command; depending on the parameter
177 you might need to rewrite the table to get the desired effects.
178 That can be done with CLUSTER [cluster(7)] or one of the forms
179 of ALTER TABLE that forces a table rewrite.
180 Note: While CREATE TABLE allows OIDS to be specified in the WITH
182 (storage_parameter) syntax, ALTER TABLE does not treat OIDS as a
183 storage parameter. Instead use the SET WITH OIDS and SET WITHOUT
184 OIDS forms to change OID status.
185 RESET ( storage_parameter [, ... ] )
188 This form resets one or more storage parameters to their
189 defaults. As with SET, a table rewrite might be needed to update
190 the table entirely.
191 INHERIT parent_table
193 This form adds the target table as a new child of the specified
194 parent table. Subsequently, queries against the parent will
195 include records of the target table. To be added as a child, the
196 target table must already contain all the same columns as the
197 parent (it could have additional columns, too). The columns must
198 have matching data types, and if they have NOT NULL constraints
199 in the parent then they must also have NOT NULL constraints in
200 the child.
201 There must also be matching child-table constraints for all
203 CHECK constraints of the parent. Currently UNIQUE, PRIMARY KEY,
204 and FOREIGN KEY constraints are not considered, but this might
205 change in the future.
206 NO INHERIT parent_table
208 This form removes the target table from the list of children of
209 the specified parent table. Queries against the parent table
210 will no longer include records drawn from the target table.
211 OWNER This form changes the owner of the table, sequence, or view to
213 the specified user.
214 SET TABLESPACE
216 This form changes the table's tablespace to the specified
217 tablespace and moves the data file(s) associated with the table
218 to the new tablespace. Indexes on the table, if any, are not
219 moved; but they can be moved separately with additional SET
220 TABLESPACE commands. See also CREATE TABLESPACE [cre‐
221 ate_tablespace(7)].
222 RENAME The RENAME forms change the name of a table (or an index,
224 sequence, or view) or the name of an individual column in a ta‐
225 ble. There is no effect on the stored data.
226 SET SCHEMA
228 This form moves the table into another schema. Associated
229 indexes, constraints, and sequences owned by table columns are
230 moved as well.
231 All the actions except RENAME and SET SCHEMA can be combined into a
233 list of multiple alterations to apply in parallel. For example, it is
234 possible to add several columns and/or alter the type of several col‐
235 umns in a single command. This is particularly useful with large
236 tables, since only one pass over the table need be made.
237 You must own the table to use ALTER TABLE. To change the schema of a
239 table, you must also have CREATE privilege on the new schema. To add
240 the table as a new child of a parent table, you must own the parent ta‐
241 ble as well. To alter the owner, you must also be a direct or indirect
242 member of the new owning role, and that role must have CREATE privilege
243 on the table's schema. (These restrictions enforce that altering the
244 owner doesn't do anything you couldn't do by dropping and recreating
245 the table. However, a superuser can alter ownership of any table any‐
246 way.)
247
249 name The name (possibly schema-qualified) of an existing table to
250 alter. If ONLY is specified, only that table is altered. If ONLY
251 is not specified, the table and any descendant tables are
252 altered.
253 column Name of a new or existing column.
255 new_column
257 New name for an existing column.
258 new_name
260 New name for the table.
261 type Data type of the new column, or new data type for an existing
263 column.
264 table_constraint
266 New table constraint for the table.
267 constraint_name
269 Name of an existing constraint to drop.
270 CASCADE
272 Automatically drop objects that depend on the dropped column or
273 constraint (for example, views referencing the column).
274 RESTRICT
276 Refuse to drop the column or constraint if there are any depen‐
277 dent objects. This is the default behavior.
278 trigger_name
280 Name of a single trigger to disable or enable.
281 ALL Disable or enable all triggers belonging to the table. (This
283 requires superuser privilege if any of the triggers are for for‐
284 eign key constraints.)
285 USER Disable or enable all triggers belonging to the table except for
287 foreign key constraint triggers.
288 index_name
290 The index name on which the table should be marked for cluster‐
291 ing.
292 storage_parameter
294 The name of a table storage parameter.
295 value The new value for a table storage parameter. This might be a
297 number or a word depending on the parameter.
298 parent_table
300 A parent table to associate or de-associate with this table.
301 new_owner
303 The user name of the new owner of the table.
304 new_tablespace
306 The name of the tablespace to which the table will be moved.
307 new_schema
309 The name of the schema to which the table will be moved.
310
312 The key word COLUMN is noise and can be omitted.
313 When a column is added with ADD COLUMN, all existing rows in the table
315 are initialized with the column's default value (NULL if no DEFAULT
316 clause is specified).
317 Adding a column with a non-null default or changing the type of an
319 existing column will require the entire table to be rewritten. This
320 might take a significant amount of time for a large table; and it will
321 temporarily require double the disk space. Adding or removing a system
322 oid column likewise requires rewriting the entire table.
323 Adding a CHECK or NOT NULL constraint requires scanning the table to
325 verify that existing rows meet the constraint.
326 The main reason for providing the option to specify multiple changes in
328 a single ALTER TABLE is that multiple table scans or rewrites can
329 thereby be combined into a single pass over the table.
330 The DROP COLUMN form does not physically remove the column, but simply
332 makes it invisible to SQL operations. Subsequent insert and update
333 operations in the table will store a null value for the column. Thus,
334 dropping a column is quick but it will not immediately reduce the on-
335 disk size of your table, as the space occupied by the dropped column is
336 not reclaimed. The space will be reclaimed over time as existing rows
337 are updated. (These statements do not apply when dropping the system
338 oid column; that is done with an immediate rewrite.)
339 The fact that ALTER TYPE requires rewriting the whole table is some‐
341 times an advantage, because the rewriting process eliminates any dead
342 space in the table. For example, to reclaim the space occupied by a
343 dropped column immediately, the fastest way is:
344 ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
346 where anycol is any remaining table column and anytype is the same type
348 that column already has. This results in no semantically-visible
349 change in the table, but the command forces rewriting, which gets rid
350 of no-longer-useful data.
351 The USING option of ALTER TYPE can actually specify any expression
353 involving the old values of the row; that is, it can refer to other
354 columns as well as the one being converted. This allows very general
355 conversions to be done with the ALTER TYPE syntax. Because of this
356 flexibility, the USING expression is not applied to the column's
357 default value (if any); the result might not be a constant expression
358 as required for a default. This means that when there is no implicit
359 or assignment cast from old to new type, ALTER TYPE might fail to con‐
360 vert the default even though a USING clause is supplied. In such cases,
361 drop the default with DROP DEFAULT, perform the ALTER TYPE, and then
362 use SET DEFAULT to add a suitable new default. Similar considerations
363 apply to indexes and constraints involving the column.
364 If a table has any descendant tables, it is not permitted to add,
366 rename, or change the type of a column in the parent table without
367 doing the same to the descendants. That is, ALTER TABLE ONLY will be
368 rejected. This ensures that the descendants always have columns match‐
369 ing the parent.
370 A recursive DROP COLUMN operation will remove a descendant table's col‐
372 umn only if the descendant does not inherit that column from any other
373 parents and never had an independent definition of the column. A nonre‐
374 cursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never
375 removes any descendant columns, but instead marks them as independently
376 defined rather than inherited.
377 The TRIGGER, CLUSTER, OWNER, and TABLESPACE actions never recurse to
379 descendant tables; that is, they always act as though ONLY were speci‐
380 fied. Adding a constraint can recurse only for CHECK constraints, and
381 is required to do so for such constraints.
382 Changing any part of a system catalog table is not permitted.
384 Refer to CREATE TABLE [create_table(7)] for a further description of
386 valid parameters. in the documentation has further information on
387 inheritance.
388
390 To add a column of type varchar to a table:
391 ALTER TABLE distributors ADD COLUMN address varchar(30);
393 To drop a column from a table:
396 ALTER TABLE distributors DROP COLUMN address RESTRICT;
398 To change the types of two existing columns in one operation:
401 ALTER TABLE distributors
403 ALTER COLUMN address TYPE varchar(80),
404 ALTER COLUMN name TYPE varchar(100);
405 To change an integer column containing UNIX timestamps to timestamp
408 with time zone via a USING clause:
409 ALTER TABLE foo
411 ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
412 USING
413 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
414 The same, when the column has a default expression that won't automati‐
417 cally cast to the new data type:
418 ALTER TABLE foo
420 ALTER COLUMN foo_timestamp DROP DEFAULT,
421 ALTER COLUMN foo_timestamp TYPE timestamp with time zone
422 USING
423 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
424 ALTER COLUMN foo_timestamp SET DEFAULT now();
425 To rename an existing column:
428 ALTER TABLE distributors RENAME COLUMN address TO city;
430 To rename an existing table:
433 ALTER TABLE distributors RENAME TO suppliers;
435 To add a not-null constraint to a column:
438 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
440 To remove a not-null constraint from a column:
442 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
444 To add a check constraint to a table and all its children:
447 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
449 To remove a check constraint from a table and all its children:
452 ALTER TABLE distributors DROP CONSTRAINT zipchk;
454 To remove a check constraint from a table only:
457 ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
459 (The check constraint remains in place for any child tables.)
461 To add a foreign key constraint to a table:
463 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
465 To add a (multicolumn) unique constraint to a table:
468 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
470 To add an automatically named primary key constraint to a table, noting
473 that a table can only ever have one primary key:
474 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
476 To move a table to a different tablespace:
479 ALTER TABLE distributors SET TABLESPACE fasttablespace;
481 To move a table to a different schema:
484 ALTER TABLE myschema.distributors SET SCHEMA yourschema;
486
489 The forms ADD, DROP, SET DEFAULT, and SET DATA TYPE (without USING)
490 conform with the SQL standard. The other forms are PostgreSQL exten‐
491 sions of the SQL standard. Also, the ability to specify more than one
492 manipulation in a single ALTER TABLE command is an extension.
493 ALTER TABLE DROP COLUMN can be used to drop the only column of a table,
495 leaving a zero-column table. This is an extension of SQL, which disal‐
496 lows zero-column tables.
497SQL - Language Statements 2011-09-22 ALTER TABLE(7)