1ALTER TABLE(7) PostgreSQL 10.7 Documentation ALTER TABLE(7)
2
6 ALTER_TABLE - change the definition of a table
7
9 ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
10 action [, ... ]
11 ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
12 RENAME [ COLUMN ] column_name TO new_column_name
13 ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
14 RENAME CONSTRAINT constraint_name TO new_constraint_name
15 ALTER TABLE [ IF EXISTS ] name
16 RENAME TO new_name
17 ALTER TABLE [ IF EXISTS ] name
18 SET SCHEMA new_schema
19 ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
20 SET TABLESPACE new_tablespace [ NOWAIT ]
21 ALTER TABLE [ IF EXISTS ] name
22 ATTACH PARTITION partition_name FOR VALUES partition_bound_spec
23 ALTER TABLE [ IF EXISTS ] name
24 DETACH PARTITION partition_name
25 where action is one of:
27 ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
29 DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
30 ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
31 ALTER [ COLUMN ] column_name SET DEFAULT expression
32 ALTER [ COLUMN ] column_name DROP DEFAULT
33 ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
34 ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
35 ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
36 ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
37 ALTER [ COLUMN ] column_name SET STATISTICS integer
38 ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
39 ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
40 ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
41 ADD table_constraint [ NOT VALID ]
42 ADD table_constraint_using_index
43 ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
44 VALIDATE CONSTRAINT constraint_name
45 DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
46 DISABLE TRIGGER [ trigger_name | ALL | USER ]
47 ENABLE TRIGGER [ trigger_name | ALL | USER ]
48 ENABLE REPLICA TRIGGER trigger_name
49 ENABLE ALWAYS TRIGGER trigger_name
50 DISABLE RULE rewrite_rule_name
51 ENABLE RULE rewrite_rule_name
52 ENABLE REPLICA RULE rewrite_rule_name
53 ENABLE ALWAYS RULE rewrite_rule_name
54 DISABLE ROW LEVEL SECURITY
55 ENABLE ROW LEVEL SECURITY
56 FORCE ROW LEVEL SECURITY
57 NO FORCE ROW LEVEL SECURITY
58 CLUSTER ON index_name
59 SET WITHOUT CLUSTER
60 SET WITH OIDS
61 SET WITHOUT OIDS
62 SET TABLESPACE new_tablespace
63 SET { LOGGED | UNLOGGED }
64 SET ( storage_parameter = value [, ... ] )
65 RESET ( storage_parameter [, ... ] )
66 INHERIT parent_table
67 NO INHERIT parent_table
68 OF type_name
69 NOT OF
70 OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
71 REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
72 and table_constraint_using_index is:
74 [ CONSTRAINT constraint_name ]
76 { UNIQUE | PRIMARY KEY } USING INDEX index_name
77 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
78
80 ALTER TABLE changes the definition of an existing table. There are
81 several subforms described below. Note that the lock level required may
82 differ for each subform. An ACCESS EXCLUSIVE lock is held unless
83 explicitly noted. When multiple subcommands are listed, the lock held
84 will be the strictest one required from any subcommand.
85 ADD COLUMN [ IF NOT EXISTS ]
87 This form adds a new column to the table, using the same syntax as
88 CREATE TABLE (CREATE_TABLE(7)). If IF NOT EXISTS is specified and a
89 column already exists with this name, no error is thrown.
90 DROP COLUMN [ IF EXISTS ]
92 This form drops a column from a table. Indexes and table
93 constraints involving the column will be automatically dropped as
94 well. Multivariate statistics referencing the dropped column will
95 also be removed if the removal of the column would cause the
96 statistics to contain data for only a single column. You will need
97 to say CASCADE if anything outside the table depends on the column,
98 for example, foreign key references or views. If IF EXISTS is
99 specified and the column does not exist, no error is thrown. In
100 this case a notice is issued instead.
101 SET DATA TYPE
103 This form changes the type of a column of a table. Indexes and
104 simple table constraints involving the column will be automatically
105 converted to use the new column type by reparsing the originally
106 supplied expression. The optional COLLATE clause specifies a
107 collation for the new column; if omitted, the collation is the
108 default for the new column type. The optional USING clause
109 specifies how to compute the new column value from the old; if
110 omitted, the default conversion is the same as an assignment cast
111 from old data type to new. A USING clause must be provided if there
112 is no implicit or assignment cast from old to new type.
113 SET/DROP DEFAULT
115 These forms set or remove the default value for a column. Default
116 values only apply in subsequent INSERT or UPDATE commands; they do
117 not cause rows already in the table to change.
118 SET/DROP NOT NULL
120 These forms change whether a column is marked to allow null values
121 or to reject null values. You can only use SET NOT NULL when the
122 column contains no null values.
123 If this table is a partition, one cannot perform DROP NOT NULL on a
125 column if it is marked NOT NULL in the parent table. To drop the
126 NOT NULL constraint from all the partitions, perform DROP NOT NULL
127 on the parent table. Even if there is no NOT NULL constraint on the
128 parent, such a constraint can still be added to individual
129 partitions, if desired; that is, the children can disallow nulls
130 even if the parent allows them, but not the other way around.
131 ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
133 SET GENERATED { ALWAYS | BY DEFAULT }
134 DROP IDENTITY [ IF EXISTS ]
135 These forms change whether a column is an identity column or change
136 the generation attribute of an existing identity column. See CREATE
137 TABLE (CREATE_TABLE(7)) for details.
138 If DROP IDENTITY IF EXISTS is specified and the column is not an
140 identity column, no error is thrown. In this case a notice is
141 issued instead.
142 SET sequence_option
144 RESTART
145 These forms alter the sequence that underlies an existing identity
146 column. sequence_option is an option supported by ALTER SEQUENCE
147 (ALTER_SEQUENCE(7)) such as INCREMENT BY.
148 SET STATISTICS
150 This form sets the per-column statistics-gathering target for
151 subsequent ANALYZE(7) operations. The target can be set in the
152 range 0 to 10000; alternatively, set it to -1 to revert to using
153 the system default statistics target (default_statistics_target).
154 For more information on the use of statistics by the PostgreSQL
155 query planner, refer to Section 14.2.
156 SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock.
158 SET ( attribute_option = value [, ... ] )
160 RESET ( attribute_option [, ... ] )
161 This form sets or resets per-attribute options. Currently, the only
162 defined per-attribute options are n_distinct and
163 n_distinct_inherited, which override the number-of-distinct-values
164 estimates made by subsequent ANALYZE(7) operations. n_distinct
165 affects the statistics for the table itself, while
166 n_distinct_inherited affects the statistics gathered for the table
167 plus its inheritance children. When set to a positive value,
168 ANALYZE will assume that the column contains exactly the specified
169 number of distinct nonnull values. When set to a negative value,
170 which must be greater than or equal to -1, ANALYZE will assume that
171 the number of distinct nonnull values in the column is linear in
172 the size of the table; the exact count is to be computed by
173 multiplying the estimated table size by the absolute value of the
174 given number. For example, a value of -1 implies that all values in
175 the column are distinct, while a value of -0.5 implies that each
176 value appears twice on the average. This can be useful when the
177 size of the table changes over time, since the multiplication by
178 the number of rows in the table is not performed until query
179 planning time. Specify a value of 0 to revert to estimating the
180 number of distinct values normally. For more information on the use
181 of statistics by the PostgreSQL query planner, refer to
182 Section 14.2.
183 Changing per-attribute options acquires a SHARE UPDATE EXCLUSIVE
185 lock.
186 SET STORAGE
188 This form sets the storage mode for a column. This controls whether
189 this column is held inline or in a secondary TOAST table, and
190 whether the data should be compressed or not. PLAIN must be used
191 for fixed-length values such as integer and is inline,
192 uncompressed. MAIN is for inline, compressible data. EXTERNAL is
193 for external, uncompressed data, and EXTENDED is for external,
194 compressed data. EXTENDED is the default for most data types that
195 support non-PLAIN storage. Use of EXTERNAL will make substring
196 operations on very large text and bytea values run faster, at the
197 penalty of increased storage space. Note that SET STORAGE doesn't
198 itself change anything in the table, it just sets the strategy to
199 be pursued during future table updates. See Section 66.2 for more
200 information.
201 ADD table_constraint [ NOT VALID ]
203 This form adds a new constraint to a table using the same syntax as
204 CREATE TABLE (CREATE_TABLE(7)), plus the option NOT VALID, which is
205 currently only allowed for foreign key and CHECK constraints. If
206 the constraint is marked NOT VALID, the potentially-lengthy initial
207 check to verify that all rows in the table satisfy the constraint
208 is skipped. The constraint will still be enforced against
209 subsequent inserts or updates (that is, they'll fail unless there
210 is a matching row in the referenced table, in the case of foreign
211 keys; and they'll fail unless the new row matches the specified
212 check constraints). But the database will not assume that the
213 constraint holds for all rows in the table, until it is validated
214 by using the VALIDATE CONSTRAINT option.
215 ADD table_constraint_using_index
217 This form adds a new PRIMARY KEY or UNIQUE constraint to a table
218 based on an existing unique index. All the columns of the index
219 will be included in the constraint.
220 The index cannot have expression columns nor be a partial index.
222 Also, it must be a b-tree index with default sort ordering. These
223 restrictions ensure that the index is equivalent to one that would
224 be built by a regular ADD PRIMARY KEY or ADD UNIQUE command.
225 If PRIMARY KEY is specified, and the index's columns are not
227 already marked NOT NULL, then this command will attempt to do ALTER
228 COLUMN SET NOT NULL against each such column. That requires a full
229 table scan to verify the column(s) contain no nulls. In all other
230 cases, this is a fast operation.
231 If a constraint name is provided then the index will be renamed to
233 match the constraint name. Otherwise the constraint will be named
234 the same as the index.
235 After this command is executed, the index is “owned” by the
237 constraint, in the same way as if the index had been built by a
238 regular ADD PRIMARY KEY or ADD UNIQUE command. In particular,
239 dropping the constraint will make the index disappear too.
240 Note
242 Adding a constraint using an existing index can be helpful in
243 situations where a new constraint needs to be added without
244 blocking table updates for a long time. To do that, create the
245 index using CREATE INDEX CONCURRENTLY, and then install it as
246 an official constraint using this syntax. See the example
247 below.
248 ALTER CONSTRAINT
250 This form alters the attributes of a constraint that was previously
251 created. Currently only foreign key constraints may be altered.
252 VALIDATE CONSTRAINT
254 This form validates a foreign key or check constraint that was
255 previously created as NOT VALID, by scanning the table to ensure
256 there are no rows for which the constraint is not satisfied.
257 Nothing happens if the constraint is already marked valid.
258 Validation can be a long process on larger tables. The value of
260 separating validation from initial creation is that you can defer
261 validation to less busy times, or can be used to give additional
262 time to correct pre-existing errors while preventing new errors.
263 Note also that validation on its own does not prevent normal write
264 commands against the table while it runs.
265 Validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table
267 being altered. If the constraint is a foreign key then a ROW SHARE
268 lock is also required on the table referenced by the constraint.
269 DROP CONSTRAINT [ IF EXISTS ]
271 This form drops the specified constraint on a table. If IF EXISTS
272 is specified and the constraint does not exist, no error is thrown.
273 In this case a notice is issued instead.
274 DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
276 These forms configure the firing of trigger(s) belonging to the
277 table. A disabled trigger is still known to the system, but is not
278 executed when its triggering event occurs. For a deferred trigger,
279 the enable status is checked when the event occurs, not when the
280 trigger function is actually executed. One can disable or enable a
281 single trigger specified by name, or all triggers on the table, or
282 only user triggers (this option excludes internally generated
283 constraint triggers such as those that are used to implement
284 foreign key constraints or deferrable uniqueness and exclusion
285 constraints). Disabling or enabling internally generated constraint
286 triggers requires superuser privileges; it should be done with
287 caution since of course the integrity of the constraint cannot be
288 guaranteed if the triggers are not executed. The trigger firing
289 mechanism is also affected by the configuration variable
290 session_replication_role. Simply enabled triggers will fire when
291 the replication role is “origin” (the default) or “local”. Triggers
292 configured as ENABLE REPLICA will only fire if the session is in
293 “replica” mode, and triggers configured as ENABLE ALWAYS will fire
294 regardless of the current replication mode.
295 This command acquires a SHARE ROW EXCLUSIVE lock.
297 DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
299 These forms configure the firing of rewrite rules belonging to the
300 table. A disabled rule is still known to the system, but is not
301 applied during query rewriting. The semantics are as for
302 disabled/enabled triggers. This configuration is ignored for ON
303 SELECT rules, which are always applied in order to keep views
304 working even if the current session is in a non-default replication
305 role.
306 DISABLE/ENABLE ROW LEVEL SECURITY
308 These forms control the application of row security policies
309 belonging to the table. If enabled and no policies exist for the
310 table, then a default-deny policy is applied. Note that policies
311 can exist for a table even if row level security is disabled - in
312 this case, the policies will NOT be applied and the policies will
313 be ignored. See also CREATE POLICY (CREATE_POLICY(7)).
314 NO FORCE/FORCE ROW LEVEL SECURITY
316 These forms control the application of row security policies
317 belonging to the table when the user is the table owner. If
318 enabled, row level security policies will be applied when the user
319 is the table owner. If disabled (the default) then row level
320 security will not be applied when the user is the table owner. See
321 also CREATE POLICY (CREATE_POLICY(7)).
322 CLUSTER ON
324 This form selects the default index for future CLUSTER(7)
325 operations. It does not actually re-cluster the table.
326 Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
328 SET WITHOUT CLUSTER
330 This form removes the most recently used CLUSTER(7) index
331 specification from the table. This affects future cluster
332 operations that don't specify an index.
333 Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
335 SET WITH OIDS
337 This form adds an oid system column to the table (see Section 5.4).
338 It does nothing if the table already has OIDs.
339 Note that this is not equivalent to ADD COLUMN oid oid; that would
341 add a normal column that happened to be named oid, not a system
342 column.
343 SET WITHOUT OIDS
345 This form removes the oid system column from the table. This is
346 exactly equivalent to DROP COLUMN oid RESTRICT, except that it will
347 not complain if there is already no oid column.
348 SET TABLESPACE
350 This form changes the table's tablespace to the specified
351 tablespace and moves the data file(s) associated with the table to
352 the new tablespace. Indexes on the table, if any, are not moved;
353 but they can be moved separately with additional SET TABLESPACE
354 commands. All tables in the current database in a tablespace can be
355 moved by using the ALL IN TABLESPACE form, which will lock all
356 tables to be moved first and then move each one. This form also
357 supports OWNED BY, which will only move tables owned by the roles
358 specified. If the NOWAIT option is specified then the command will
359 fail if it is unable to acquire all of the locks required
360 immediately. Note that system catalogs are not moved by this
361 command, use ALTER DATABASE or explicit ALTER TABLE invocations
362 instead if desired. The information_schema relations are not
363 considered part of the system catalogs and will be moved. See also
364 CREATE TABLESPACE (CREATE_TABLESPACE(7)).
365 SET { LOGGED | UNLOGGED }
367 This form changes the table from unlogged to logged or vice-versa
368 (see UNLOGGED). It cannot be applied to a temporary table.
369 SET ( storage_parameter = value [, ... ] )
371 This form changes one or more storage parameters for the table. See
372 Storage Parameters for details on the available parameters. Note
373 that the table contents will not be modified immediately by this
374 command; depending on the parameter you might need to rewrite the
375 table to get the desired effects. That can be done with VACUUM
376 FULL, CLUSTER(7) or one of the forms of ALTER TABLE that forces a
377 table rewrite. For planner related parameters, changes will take
378 effect from the next time the table is locked so currently
379 executing queries will not be affected.
380 SHARE UPDATE EXCLUSIVE lock will be taken for fillfactor and
382 autovacuum storage parameters, as well as the following planner
383 related parameters: effective_io_concurrency, parallel_workers,
384 seq_page_cost, random_page_cost, n_distinct and
385 n_distinct_inherited.
386 Note
388 While CREATE TABLE allows OIDS to be specified in the WITH
389 (storage_parameter) syntax, ALTER TABLE does not treat OIDS as
390 a storage parameter. Instead use the SET WITH OIDS and SET
391 WITHOUT OIDS forms to change OID status.
392 RESET ( storage_parameter [, ... ] )
394 This form resets one or more storage parameters to their defaults.
395 As with SET, a table rewrite might be needed to update the table
396 entirely.
397 INHERIT parent_table
399 This form adds the target table as a new child of the specified
400 parent table. Subsequently, queries against the parent will include
401 records of the target table. To be added as a child, the target
402 table must already contain all the same columns as the parent (it
403 could have additional columns, too). The columns must have matching
404 data types, and if they have NOT NULL constraints in the parent
405 then they must also have NOT NULL constraints in the child.
406 There must also be matching child-table constraints for all CHECK
408 constraints of the parent, except those marked non-inheritable
409 (that is, created with ALTER TABLE ... ADD CONSTRAINT ... NO
410 INHERIT) in the parent, which are ignored; all child-table
411 constraints matched must not be marked non-inheritable. Currently
412 UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are not
413 considered, but this might change in the future.
414 NO INHERIT parent_table
416 This form removes the target table from the list of children of the
417 specified parent table. Queries against the parent table will no
418 longer include records drawn from the target table.
419 OF type_name
421 This form links the table to a composite type as though CREATE
422 TABLE OF had formed it. The table's list of column names and types
423 must precisely match that of the composite type; the presence of an
424 oid system column is permitted to differ. The table must not
425 inherit from any other table. These restrictions ensure that CREATE
426 TABLE OF would permit an equivalent table definition.
427 NOT OF
429 This form dissociates a typed table from its type.
430 OWNER
432 This form changes the owner of the table, sequence, view,
433 materialized view, or foreign table to the specified user.
434 REPLICA IDENTITY
436 This form changes the information which is written to the
437 write-ahead log to identify rows which are updated or deleted. This
438 option has no effect except when logical replication is in use.
439 DEFAULT (the default for non-system tables) records the old values
440 of the columns of the primary key, if any. USING INDEX records the
441 old values of the columns covered by the named index, which must be
442 unique, not partial, not deferrable, and include only columns
443 marked NOT NULL. FULL records the old values of all columns in the
444 row. NOTHING records no information about the old row. (This is
445 the default for system tables.) In all cases, no old values are
446 logged unless at least one of the columns that would be logged
447 differs between the old and new versions of the row.
448 RENAME
450 The RENAME forms change the name of a table (or an index, sequence,
451 view, materialized view, or foreign table), the name of an
452 individual column in a table, or the name of a constraint of the
453 table. There is no effect on the stored data.
454 SET SCHEMA
456 This form moves the table into another schema. Associated indexes,
457 constraints, and sequences owned by table columns are moved as
458 well.
459 ATTACH PARTITION partition_name FOR VALUES partition_bound_spec
461 This form attaches an existing table (which might itself be
462 partitioned) as a partition of the target table using the same
463 syntax for partition_bound_spec as CREATE TABLE (CREATE_TABLE(7)).
464 The partition bound specification must correspond to the
465 partitioning strategy and partition key of the target table. The
466 table to be attached must have all the same columns as the target
467 table and no more; moreover, the column types must also match.
468 Also, it must have all the NOT NULL and CHECK constraints of the
469 target table. Currently UNIQUE, PRIMARY KEY, and FOREIGN KEY
470 constraints are not considered. If any of the CHECK constraints of
471 the table being attached is marked NO INHERIT, the command will
472 fail; such a constraint must be recreated without the NO INHERIT
473 clause.
474 If the new partition is a regular table, a full table scan is
476 performed to check that no existing row in the table violates the
477 partition constraint. It is possible to avoid this scan by adding a
478 valid CHECK constraint to the table that would allow only the rows
479 satisfying the desired partition constraint before running this
480 command. It will be determined using such a constraint that the
481 table need not be scanned to validate the partition constraint.
482 This does not work, however, if any of the partition keys is an
483 expression and the partition does not accept NULL values. If
484 attaching a list partition that will not accept NULL values, also
485 add NOT NULL constraint to the partition key column, unless it's an
486 expression.
487 If the new partition is a foreign table, nothing is done to verify
489 that all the rows in the foreign table obey the partition
490 constraint. (See the discussion in CREATE FOREIGN TABLE
491 (CREATE_FOREIGN_TABLE(7)) about constraints on the foreign table.)
492 DETACH PARTITION partition_name
494 This form detaches specified partition of the target table. The
495 detached partition continues to exist as a standalone table, but no
496 longer has any ties to the table from which it was detached.
497 All the forms of ALTER TABLE that act on a single table, except RENAME,
499 SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION can be combined into
500 a list of multiple alterations to be applied together. For example, it
501 is possible to add several columns and/or alter the type of several
502 columns in a single command. This is particularly useful with large
503 tables, since only one pass over the table need be made.
504 You must own the table to use ALTER TABLE. To change the schema or
506 tablespace of a table, you must also have CREATE privilege on the new
507 schema or tablespace. To add the table as a new child of a parent
508 table, you must own the parent table as well. Also, to attach a table
509 as a new partition of the table, you must own the table being attached.
510 To alter the owner, you must also be a direct or indirect member of the
511 new owning role, and that role must have CREATE privilege on the
512 table's schema. (These restrictions enforce that altering the owner
513 doesn't do anything you couldn't do by dropping and recreating the
514 table. However, a superuser can alter ownership of any table anyway.)
515 To add a column or alter a column type or use the OF clause, you must
516 also have USAGE privilege on the data type.
517
519 IF EXISTS
520 Do not throw an error if the table does not exist. A notice is
521 issued in this case.
522 name
524 The name (optionally schema-qualified) of an existing table to
525 alter. If ONLY is specified before the table name, only that table
526 is altered. If ONLY is not specified, the table and all its
527 descendant tables (if any) are altered. Optionally, * can be
528 specified after the table name to explicitly indicate that
529 descendant tables are included.
530 column_name
532 Name of a new or existing column.
533 new_column_name
535 New name for an existing column.
536 new_name
538 New name for the table.
539 data_type
541 Data type of the new column, or new data type for an existing
542 column.
543 table_constraint
545 New table constraint for the table.
546 constraint_name
548 Name of a new or existing constraint.
549 CASCADE
551 Automatically drop objects that depend on the dropped column or
552 constraint (for example, views referencing the column), and in turn
553 all objects that depend on those objects (see Section 5.13).
554 RESTRICT
556 Refuse to drop the column or constraint if there are any dependent
557 objects. This is the default behavior.
558 trigger_name
560 Name of a single trigger to disable or enable.
561 ALL
563 Disable or enable all triggers belonging to the table. (This
564 requires superuser privilege if any of the triggers are internally
565 generated constraint triggers such as those that are used to
566 implement foreign key constraints or deferrable uniqueness and
567 exclusion constraints.)
568 USER
570 Disable or enable all triggers belonging to the table except for
571 internally generated constraint triggers such as those that are
572 used to implement foreign key constraints or deferrable uniqueness
573 and exclusion constraints.
574 index_name
576 The name of an existing index.
577 storage_parameter
579 The name of a table storage parameter.
580 value
582 The new value for a table storage parameter. This might be a number
583 or a word depending on the parameter.
584 parent_table
586 A parent table to associate or de-associate with this table.
587 new_owner
589 The user name of the new owner of the table.
590 new_tablespace
592 The name of the tablespace to which the table will be moved.
593 new_schema
595 The name of the schema to which the table will be moved.
596 partition_name
598 The name of the table to attach as a new partition or to detach
599 from this table.
600 partition_bound_spec
602 The partition bound specification for a new partition. Refer to
603 CREATE TABLE (CREATE_TABLE(7)) for more details on the syntax of
604 the same.
605
607 The key word COLUMN is noise and can be omitted.
608 When a column is added with ADD COLUMN, all existing rows in the table
610 are initialized with the column's default value (NULL if no DEFAULT
611 clause is specified). If there is no DEFAULT clause, this is merely a
612 metadata change and does not require any immediate update of the
613 table's data; the added NULL values are supplied on readout, instead.
614 Adding a column with a DEFAULT clause or changing the type of an
616 existing column will require the entire table and its indexes to be
617 rewritten. As an exception when changing the type of an existing
618 column, if the USING clause does not change the column contents and the
619 old type is either binary coercible to the new type or an unconstrained
620 domain over the new type, a table rewrite is not needed; but any
621 indexes on the affected columns must still be rebuilt. Adding or
622 removing a system oid column also requires rewriting the entire table.
623 Table and/or index rebuilds may take a significant amount of time for a
624 large table; and will temporarily require as much as double the disk
625 space.
626 Adding a CHECK or NOT NULL constraint requires scanning the table to
628 verify that existing rows meet the constraint, but does not require a
629 table rewrite.
630 Similarly, when attaching a new partition it may be scanned to verify
632 that existing rows meet the partition constraint.
633 The main reason for providing the option to specify multiple changes in
635 a single ALTER TABLE is that multiple table scans or rewrites can
636 thereby be combined into a single pass over the table.
637 The DROP COLUMN form does not physically remove the column, but simply
639 makes it invisible to SQL operations. Subsequent insert and update
640 operations in the table will store a null value for the column. Thus,
641 dropping a column is quick but it will not immediately reduce the
642 on-disk size of your table, as the space occupied by the dropped column
643 is not reclaimed. The space will be reclaimed over time as existing
644 rows are updated. (These statements do not apply when dropping the
645 system oid column; that is done with an immediate rewrite.)
646 To force immediate reclamation of space occupied by a dropped column,
648 you can execute one of the forms of ALTER TABLE that performs a rewrite
649 of the whole table. This results in reconstructing each row with the
650 dropped column replaced by a null value.
651 The rewriting forms of ALTER TABLE are not MVCC-safe. After a table
653 rewrite, the table will appear empty to concurrent transactions, if
654 they are using a snapshot taken before the rewrite occurred. See
655 Section 13.5 for more details.
656 The USING option of SET DATA TYPE can actually specify any expression
658 involving the old values of the row; that is, it can refer to other
659 columns as well as the one being converted. This allows very general
660 conversions to be done with the SET DATA TYPE syntax. Because of this
661 flexibility, the USING expression is not applied to the column's
662 default value (if any); the result might not be a constant expression
663 as required for a default. This means that when there is no implicit or
664 assignment cast from old to new type, SET DATA TYPE might fail to
665 convert the default even though a USING clause is supplied. In such
666 cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and
667 then use SET DEFAULT to add a suitable new default. Similar
668 considerations apply to indexes and constraints involving the column.
669 If a table has any descendant tables, it is not permitted to add,
671 rename, or change the type of a column in the parent table without
672 doing same to the descendants. This ensures that the descendants always
673 have columns matching the parent. Similarly, a constraint cannot be
674 renamed in the parent without also renaming it in all descendants, so
675 that constraints also match between the parent and its descendants.
676 Also, because selecting from the parent also selects from its
677 descendants, a constraint on the parent cannot be marked valid unless
678 it is also marked valid for those descendants. In all of these cases,
679 ALTER TABLE ONLY will be rejected.
680 A recursive DROP COLUMN operation will remove a descendant table's
682 column only if the descendant does not inherit that column from any
683 other parents and never had an independent definition of the column. A
684 nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never
685 removes any descendant columns, but instead marks them as independently
686 defined rather than inherited. A nonrecursive DROP COLUMN command will
687 fail for a partitioned table, because all partitions of a table must
688 have the same columns as the partitioning root.
689 The actions for identity columns (ADD GENERATED, SET etc., DROP
691 IDENTITY), as well as the actions TRIGGER, CLUSTER, OWNER, and
692 TABLESPACE never recurse to descendant tables; that is, they always act
693 as though ONLY were specified. Adding a constraint recurses only for
694 CHECK constraints that are not marked NO INHERIT.
695 Changing any part of a system catalog table is not permitted.
697 Refer to CREATE TABLE (CREATE_TABLE(7)) for a further description of
699 valid parameters. Chapter 5 has further information on inheritance.
700
702 To add a column of type varchar to a table:
703 ALTER TABLE distributors ADD COLUMN address varchar(30);
705 To drop a column from a table:
707 ALTER TABLE distributors DROP COLUMN address RESTRICT;
709 To change the types of two existing columns in one operation:
711 ALTER TABLE distributors
713 ALTER COLUMN address TYPE varchar(80),
714 ALTER COLUMN name TYPE varchar(100);
715 To change an integer column containing Unix timestamps to timestamp
717 with time zone via a USING clause:
718 ALTER TABLE foo
720 ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
721 USING
722 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
723 The same, when the column has a default expression that won't
725 automatically cast to the new data type:
726 ALTER TABLE foo
728 ALTER COLUMN foo_timestamp DROP DEFAULT,
729 ALTER COLUMN foo_timestamp TYPE timestamp with time zone
730 USING
731 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
732 ALTER COLUMN foo_timestamp SET DEFAULT now();
733 To rename an existing column:
735 ALTER TABLE distributors RENAME COLUMN address TO city;
737 To rename an existing table:
739 ALTER TABLE distributors RENAME TO suppliers;
741 To rename an existing constraint:
743 ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
745 To add a not-null constraint to a column:
747 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
749 To remove a not-null constraint from a column:
751 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
753 To add a check constraint to a table and all its children:
755 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
757 To add a check constraint only to a table and not to its children:
759 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
761 (The check constraint will not be inherited by future children,
763 either.)
764 To remove a check constraint from a table and all its children:
766 ALTER TABLE distributors DROP CONSTRAINT zipchk;
768 To remove a check constraint from one table only:
770 ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
772 (The check constraint remains in place for any child tables.)
774 To add a foreign key constraint to a table:
776 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
778 To add a foreign key constraint to a table with the least impact on
780 other work:
781 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
783 ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
784 To add a (multicolumn) unique constraint to a table:
786 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
788 To add an automatically named primary key constraint to a table, noting
790 that a table can only ever have one primary key:
791 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
793 To move a table to a different tablespace:
795 ALTER TABLE distributors SET TABLESPACE fasttablespace;
797 To move a table to a different schema:
799 ALTER TABLE myschema.distributors SET SCHEMA yourschema;
801 To recreate a primary key constraint, without blocking updates while
803 the index is rebuilt:
804 CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
806 ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
807 ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
808 Attach a partition to range partitioned table:
810 ALTER TABLE measurement
812 ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
813 Attach a partition to list partitioned table:
815 ALTER TABLE cities
817 ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
818 Detach a partition from partitioned table:
820 ALTER TABLE measurement
822 DETACH PARTITION measurement_y2015m12;
823
825 The forms ADD (without USING INDEX), DROP [COLUMN], DROP IDENTITY,
826 RESTART, SET DEFAULT, SET DATA TYPE (without USING), SET GENERATED, and
827 SET sequence_option conform with the SQL standard. The other forms are
828 PostgreSQL extensions of the SQL standard. Also, the ability to specify
829 more than one manipulation in a single ALTER TABLE command is an
830 extension.
831 ALTER TABLE DROP COLUMN can be used to drop the only column of a table,
833 leaving a zero-column table. This is an extension of SQL, which
834 disallows zero-column tables.
835
837 CREATE TABLE (CREATE_TABLE(7))
838PostgreSQL 10.7 2019 ALTER TABLE(7)