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 (optionally schema-qualified) of an existing table to
250 alter. If ONLY is specified before the table name, only that ta‐
251 ble is altered. If ONLY is not specified, the table and all its
252 descendant tables (if any) are altered. Optionally, * can be
253 specified after the table name to explicitly indicate that
254 descendant tables are included.
255 column Name of a new or existing column.
257 new_column
259 New name for an existing column.
260 new_name
262 New name for the table.
263 type Data type of the new column, or new data type for an existing
265 column.
266 table_constraint
268 New table constraint for the table.
269 constraint_name
271 Name of an existing constraint to drop.
272 CASCADE
274 Automatically drop objects that depend on the dropped column or
275 constraint (for example, views referencing the column).
276 RESTRICT
278 Refuse to drop the column or constraint if there are any depen‐
279 dent objects. This is the default behavior.
280 trigger_name
282 Name of a single trigger to disable or enable.
283 ALL Disable or enable all triggers belonging to the table. (This
285 requires superuser privilege if any of the triggers are for for‐
286 eign key constraints.)
287 USER Disable or enable all triggers belonging to the table except for
289 foreign key constraint triggers.
290 index_name
292 The index name on which the table should be marked for cluster‐
293 ing.
294 storage_parameter
296 The name of a table storage parameter.
297 value The new value for a table storage parameter. This might be a
299 number or a word depending on the parameter.
300 parent_table
302 A parent table to associate or de-associate with this table.
303 new_owner
305 The user name of the new owner of the table.
306 new_tablespace
308 The name of the tablespace to which the table will be moved.
309 new_schema
311 The name of the schema to which the table will be moved.
312
314 The key word COLUMN is noise and can be omitted.
315 When a column is added with ADD COLUMN, all existing rows in the table
317 are initialized with the column's default value (NULL if no DEFAULT
318 clause is specified).
319 Adding a column with a non-null default or changing the type of an
321 existing column will require the entire table to be rewritten. This
322 might take a significant amount of time for a large table; and it will
323 temporarily require double the disk space. Adding or removing a system
324 oid column likewise requires rewriting the entire table.
325 Adding a CHECK or NOT NULL constraint requires scanning the table to
327 verify that existing rows meet the constraint.
328 The main reason for providing the option to specify multiple changes in
330 a single ALTER TABLE is that multiple table scans or rewrites can
331 thereby be combined into a single pass over the table.
332 The DROP COLUMN form does not physically remove the column, but simply
334 makes it invisible to SQL operations. Subsequent insert and update
335 operations in the table will store a null value for the column. Thus,
336 dropping a column is quick but it will not immediately reduce the on-
337 disk size of your table, as the space occupied by the dropped column is
338 not reclaimed. The space will be reclaimed over time as existing rows
339 are updated. (These statements do not apply when dropping the system
340 oid column; that is done with an immediate rewrite.)
341 The fact that ALTER TYPE requires rewriting the whole table is some‐
343 times an advantage, because the rewriting process eliminates any dead
344 space in the table. For example, to reclaim the space occupied by a
345 dropped column immediately, the fastest way is:
346 ALTER TABLE table ALTER COLUMN anycol TYPE anytype;
348 where anycol is any remaining table column and anytype is the same type
350 that column already has. This results in no semantically-visible
351 change in the table, but the command forces rewriting, which gets rid
352 of no-longer-useful data.
353 The USING option of ALTER TYPE can actually specify any expression
355 involving the old values of the row; that is, it can refer to other
356 columns as well as the one being converted. This allows very general
357 conversions to be done with the ALTER TYPE syntax. Because of this
358 flexibility, the USING expression is not applied to the column's
359 default value (if any); the result might not be a constant expression
360 as required for a default. This means that when there is no implicit
361 or assignment cast from old to new type, ALTER TYPE might fail to con‐
362 vert the default even though a USING clause is supplied. In such cases,
363 drop the default with DROP DEFAULT, perform the ALTER TYPE, and then
364 use SET DEFAULT to add a suitable new default. Similar considerations
365 apply to indexes and constraints involving the column.
366 If a table has any descendant tables, it is not permitted to add,
368 rename, or change the type of a column in the parent table without
369 doing the same to the descendants. That is, ALTER TABLE ONLY will be
370 rejected. This ensures that the descendants always have columns match‐
371 ing the parent.
372 A recursive DROP COLUMN operation will remove a descendant table's col‐
374 umn only if the descendant does not inherit that column from any other
375 parents and never had an independent definition of the column. A nonre‐
376 cursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never
377 removes any descendant columns, but instead marks them as independently
378 defined rather than inherited.
379 The TRIGGER, CLUSTER, OWNER, and TABLESPACE actions never recurse to
381 descendant tables; that is, they always act as though ONLY were speci‐
382 fied. Adding a constraint can recurse only for CHECK constraints, and
383 is required to do so for such constraints.
384 Changing any part of a system catalog table is not permitted.
386 Refer to CREATE TABLE [create_table(7)] for a further description of
388 valid parameters. in the documentation has further information on
389 inheritance.
390
392 To add a column of type varchar to a table:
393 ALTER TABLE distributors ADD COLUMN address varchar(30);
395 To drop a column from a table:
398 ALTER TABLE distributors DROP COLUMN address RESTRICT;
400 To change the types of two existing columns in one operation:
403 ALTER TABLE distributors
405 ALTER COLUMN address TYPE varchar(80),
406 ALTER COLUMN name TYPE varchar(100);
407 To change an integer column containing UNIX timestamps to timestamp
410 with time zone via a USING clause:
411 ALTER TABLE foo
413 ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
414 USING
415 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
416 The same, when the column has a default expression that won't automati‐
419 cally cast to the new data type:
420 ALTER TABLE foo
422 ALTER COLUMN foo_timestamp DROP DEFAULT,
423 ALTER COLUMN foo_timestamp TYPE timestamp with time zone
424 USING
425 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
426 ALTER COLUMN foo_timestamp SET DEFAULT now();
427 To rename an existing column:
430 ALTER TABLE distributors RENAME COLUMN address TO city;
432 To rename an existing table:
435 ALTER TABLE distributors RENAME TO suppliers;
437 To add a not-null constraint to a column:
440 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
442 To remove a not-null constraint from a column:
444 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
446 To add a check constraint to a table and all its children:
449 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
451 To remove a check constraint from a table and all its children:
454 ALTER TABLE distributors DROP CONSTRAINT zipchk;
456 To remove a check constraint from one table only:
459 ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
461 (The check constraint remains in place for any child tables.)
463 To add a foreign key constraint to a table:
465 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
467 To add a (multicolumn) unique constraint to a table:
470 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
472 To add an automatically named primary key constraint to a table, noting
475 that a table can only ever have one primary key:
476 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
478 To move a table to a different tablespace:
481 ALTER TABLE distributors SET TABLESPACE fasttablespace;
483 To move a table to a different schema:
486 ALTER TABLE myschema.distributors SET SCHEMA yourschema;
488
491 The forms ADD, DROP, SET DEFAULT, and SET DATA TYPE (without USING)
492 conform with the SQL standard. The other forms are PostgreSQL exten‐
493 sions of the SQL standard. Also, the ability to specify more than one
494 manipulation in a single ALTER TABLE command is an extension.
495 ALTER TABLE DROP COLUMN can be used to drop the only column of a table,
497 leaving a zero-column table. This is an extension of SQL, which disal‐
498 lows zero-column tables.
499SQL - Language Statements 2014-02-17 ALTER TABLE(7)