1ALTER TABLE(7) PostgreSQL 12.2 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 | DEFAULT }
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 WITHOUT OIDS
61 SET TABLESPACE new_tablespace
62 SET { LOGGED | UNLOGGED }
63 SET ( storage_parameter = value [, ... ] )
64 RESET ( storage_parameter [, ... ] )
65 INHERIT parent_table
66 NO INHERIT parent_table
67 OF type_name
68 NOT OF
69 OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
70 REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
71 and partition_bound_spec is:
73 IN ( partition_bound_expr [, ...] ) |
75 FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
76 TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
77 WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
78 and column_constraint is:
80 [ CONSTRAINT constraint_name ]
82 { NOT NULL |
83 NULL |
84 CHECK ( expression ) [ NO INHERIT ] |
85 DEFAULT default_expr |
86 GENERATED ALWAYS AS ( generation_expr ) STORED |
87 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
88 UNIQUE index_parameters |
89 PRIMARY KEY index_parameters |
90 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
91 [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
92 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
93 and table_constraint is:
95 [ CONSTRAINT constraint_name ]
97 { CHECK ( expression ) [ NO INHERIT ] |
98 UNIQUE ( column_name [, ... ] ) index_parameters |
99 PRIMARY KEY ( column_name [, ... ] ) index_parameters |
100 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
101 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
102 [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
103 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
104 and table_constraint_using_index is:
106 [ CONSTRAINT constraint_name ]
108 { UNIQUE | PRIMARY KEY } USING INDEX index_name
109 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
110 index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
112 [ INCLUDE ( column_name [, ... ] ) ]
114 [ WITH ( storage_parameter [= value] [, ... ] ) ]
115 [ USING INDEX TABLESPACE tablespace_name ]
116 exclude_element in an EXCLUDE constraint is:
118 { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
120
122 ALTER TABLE changes the definition of an existing table. There are
123 several subforms described below. Note that the lock level required may
124 differ for each subform. An ACCESS EXCLUSIVE lock is held unless
125 explicitly noted. When multiple subcommands are listed, the lock held
126 will be the strictest one required from any subcommand.
127 ADD COLUMN [ IF NOT EXISTS ]
129 This form adds a new column to the table, using the same syntax as
130 CREATE TABLE (CREATE_TABLE(7)). If IF NOT EXISTS is specified and a
131 column already exists with this name, no error is thrown.
132 DROP COLUMN [ IF EXISTS ]
134 This form drops a column from a table. Indexes and table
135 constraints involving the column will be automatically dropped as
136 well. Multivariate statistics referencing the dropped column will
137 also be removed if the removal of the column would cause the
138 statistics to contain data for only a single column. You will need
139 to say CASCADE if anything outside the table depends on the column,
140 for example, foreign key references or views. If IF EXISTS is
141 specified and the column does not exist, no error is thrown. In
142 this case a notice is issued instead.
143 SET DATA TYPE
145 This form changes the type of a column of a table. Indexes and
146 simple table constraints involving the column will be automatically
147 converted to use the new column type by reparsing the originally
148 supplied expression. The optional COLLATE clause specifies a
149 collation for the new column; if omitted, the collation is the
150 default for the new column type. The optional USING clause
151 specifies how to compute the new column value from the old; if
152 omitted, the default conversion is the same as an assignment cast
153 from old data type to new. A USING clause must be provided if there
154 is no implicit or assignment cast from old to new type.
155 SET/DROP DEFAULT
157 These forms set or remove the default value for a column. Default
158 values only apply in subsequent INSERT or UPDATE commands; they do
159 not cause rows already in the table to change.
160 SET/DROP NOT NULL
162 These forms change whether a column is marked to allow null values
163 or to reject null values.
164 SET NOT NULL may only be applied to a column provided none of the
166 records in the table contain a NULL value for the column.
167 Ordinarily this is checked during the ALTER TABLE by scanning the
168 entire table; however, if a valid CHECK constraint is found which
169 proves no NULL can exist, then the table scan is skipped.
170 If this table is a partition, one cannot perform DROP NOT NULL on a
172 column if it is marked NOT NULL in the parent table. To drop the
173 NOT NULL constraint from all the partitions, perform DROP NOT NULL
174 on the parent table. Even if there is no NOT NULL constraint on the
175 parent, such a constraint can still be added to individual
176 partitions, if desired; that is, the children can disallow nulls
177 even if the parent allows them, but not the other way around.
178 ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
180 SET GENERATED { ALWAYS | BY DEFAULT }
181 DROP IDENTITY [ IF EXISTS ]
182 These forms change whether a column is an identity column or change
183 the generation attribute of an existing identity column. See CREATE
184 TABLE (CREATE_TABLE(7)) for details.
185 If DROP IDENTITY IF EXISTS is specified and the column is not an
187 identity column, no error is thrown. In this case a notice is
188 issued instead.
189 SET sequence_option
191 RESTART
192 These forms alter the sequence that underlies an existing identity
193 column. sequence_option is an option supported by ALTER SEQUENCE
194 (ALTER_SEQUENCE(7)) such as INCREMENT BY.
195 SET STATISTICS
197 This form sets the per-column statistics-gathering target for
198 subsequent ANALYZE(7) operations. The target can be set in the
199 range 0 to 10000; alternatively, set it to -1 to revert to using
200 the system default statistics target (default_statistics_target).
201 For more information on the use of statistics by the PostgreSQL
202 query planner, refer to Section 14.2.
203 SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock.
205 SET ( attribute_option = value [, ... ] )
207 RESET ( attribute_option [, ... ] )
208 This form sets or resets per-attribute options. Currently, the only
209 defined per-attribute options are n_distinct and
210 n_distinct_inherited, which override the number-of-distinct-values
211 estimates made by subsequent ANALYZE(7) operations. n_distinct
212 affects the statistics for the table itself, while
213 n_distinct_inherited affects the statistics gathered for the table
214 plus its inheritance children. When set to a positive value,
215 ANALYZE will assume that the column contains exactly the specified
216 number of distinct nonnull values. When set to a negative value,
217 which must be greater than or equal to -1, ANALYZE will assume that
218 the number of distinct nonnull values in the column is linear in
219 the size of the table; the exact count is to be computed by
220 multiplying the estimated table size by the absolute value of the
221 given number. For example, a value of -1 implies that all values in
222 the column are distinct, while a value of -0.5 implies that each
223 value appears twice on the average. This can be useful when the
224 size of the table changes over time, since the multiplication by
225 the number of rows in the table is not performed until query
226 planning time. Specify a value of 0 to revert to estimating the
227 number of distinct values normally. For more information on the use
228 of statistics by the PostgreSQL query planner, refer to
229 Section 14.2.
230 Changing per-attribute options acquires a SHARE UPDATE EXCLUSIVE
232 lock.
233 SET STORAGE
235 This form sets the storage mode for a column. This controls whether
236 this column is held inline or in a secondary TOAST table, and
237 whether the data should be compressed or not. PLAIN must be used
238 for fixed-length values such as integer and is inline,
239 uncompressed. MAIN is for inline, compressible data. EXTERNAL is
240 for external, uncompressed data, and EXTENDED is for external,
241 compressed data. EXTENDED is the default for most data types that
242 support non-PLAIN storage. Use of EXTERNAL will make substring
243 operations on very large text and bytea values run faster, at the
244 penalty of increased storage space. Note that SET STORAGE doesn't
245 itself change anything in the table, it just sets the strategy to
246 be pursued during future table updates. See Section 68.2 for more
247 information.
248 ADD table_constraint [ NOT VALID ]
250 This form adds a new constraint to a table using the same syntax as
251 CREATE TABLE (CREATE_TABLE(7)), plus the option NOT VALID, which is
252 currently only allowed for foreign key and CHECK constraints.
253 Normally, this form will cause a scan of the table to verify that
255 all existing rows in the table satisfy the new constraint. But if
256 the NOT VALID option is used, this potentially-lengthy scan is
257 skipped. The constraint will still be enforced against subsequent
258 inserts or updates (that is, they'll fail unless there is a
259 matching row in the referenced table, in the case of foreign keys,
260 or they'll fail unless the new row matches the specified check
261 condition). But the database will not assume that the constraint
262 holds for all rows in the table, until it is validated by using the
263 VALIDATE CONSTRAINT option. See NOTES below for more information
264 about using the NOT VALID option.
265 Addition of a foreign key constraint requires a SHARE ROW EXCLUSIVE
267 lock on the referenced table, in addition to the lock on the table
268 receiving the constraint.
269 Additional restrictions apply when unique or primary key
271 constraints are added to partitioned tables; see CREATE TABLE
272 (CREATE_TABLE(7)). Also, foreign key constraints on partitioned
273 tables may not be declared NOT VALID at present.
274 ADD table_constraint_using_index
276 This form adds a new PRIMARY KEY or UNIQUE constraint to a table
277 based on an existing unique index. All the columns of the index
278 will be included in the constraint.
279 The index cannot have expression columns nor be a partial index.
281 Also, it must be a b-tree index with default sort ordering. These
282 restrictions ensure that the index is equivalent to one that would
283 be built by a regular ADD PRIMARY KEY or ADD UNIQUE command.
284 If PRIMARY KEY is specified, and the index's columns are not
286 already marked NOT NULL, then this command will attempt to do ALTER
287 COLUMN SET NOT NULL against each such column. That requires a full
288 table scan to verify the column(s) contain no nulls. In all other
289 cases, this is a fast operation.
290 If a constraint name is provided then the index will be renamed to
292 match the constraint name. Otherwise the constraint will be named
293 the same as the index.
294 After this command is executed, the index is “owned” by the
296 constraint, in the same way as if the index had been built by a
297 regular ADD PRIMARY KEY or ADD UNIQUE command. In particular,
298 dropping the constraint will make the index disappear too.
299 This form is not currently supported on partitioned tables.
301 Note
303 Adding a constraint using an existing index can be helpful in
304 situations where a new constraint needs to be added without
305 blocking table updates for a long time. To do that, create the
306 index using CREATE INDEX CONCURRENTLY, and then install it as
307 an official constraint using this syntax. See the example
308 below.
309 ALTER CONSTRAINT
311 This form alters the attributes of a constraint that was previously
312 created. Currently only foreign key constraints may be altered.
313 VALIDATE CONSTRAINT
315 This form validates a foreign key or check constraint that was
316 previously created as NOT VALID, by scanning the table to ensure
317 there are no rows for which the constraint is not satisfied.
318 Nothing happens if the constraint is already marked valid. (See
319 NOTES below for an explanation of the usefulness of this command.)
320 DROP CONSTRAINT [ IF EXISTS ]
322 This form drops the specified constraint on a table, along with any
323 index underlying the constraint. If IF EXISTS is specified and the
324 constraint does not exist, no error is thrown. In this case a
325 notice is issued instead.
326 DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
328 These forms configure the firing of trigger(s) belonging to the
329 table. A disabled trigger is still known to the system, but is not
330 executed when its triggering event occurs. For a deferred trigger,
331 the enable status is checked when the event occurs, not when the
332 trigger function is actually executed. One can disable or enable a
333 single trigger specified by name, or all triggers on the table, or
334 only user triggers (this option excludes internally generated
335 constraint triggers such as those that are used to implement
336 foreign key constraints or deferrable uniqueness and exclusion
337 constraints). Disabling or enabling internally generated constraint
338 triggers requires superuser privileges; it should be done with
339 caution since of course the integrity of the constraint cannot be
340 guaranteed if the triggers are not executed.
341 The trigger firing mechanism is also affected by the configuration
343 variable session_replication_role. Simply enabled triggers (the
344 default) will fire when the replication role is “origin” (the
345 default) or “local”. Triggers configured as ENABLE REPLICA will
346 only fire if the session is in “replica” mode, and triggers
347 configured as ENABLE ALWAYS will fire regardless of the current
348 replication role.
349 The effect of this mechanism is that in the default configuration,
351 triggers do not fire on replicas. This is useful because if a
352 trigger is used on the origin to propagate data between tables,
353 then the replication system will also replicate the propagated
354 data, and the trigger should not fire a second time on the replica,
355 because that would lead to duplication. However, if a trigger is
356 used for another purpose such as creating external alerts, then it
357 might be appropriate to set it to ENABLE ALWAYS so that it is also
358 fired on replicas.
359 This command acquires a SHARE ROW EXCLUSIVE lock.
361 DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
363 These forms configure the firing of rewrite rules belonging to the
364 table. A disabled rule is still known to the system, but is not
365 applied during query rewriting. The semantics are as for
366 disabled/enabled triggers. This configuration is ignored for ON
367 SELECT rules, which are always applied in order to keep views
368 working even if the current session is in a non-default replication
369 role.
370 The rule firing mechanism is also affected by the configuration
372 variable session_replication_role, analogous to triggers as
373 described above.
374 DISABLE/ENABLE ROW LEVEL SECURITY
376 These forms control the application of row security policies
377 belonging to the table. If enabled and no policies exist for the
378 table, then a default-deny policy is applied. Note that policies
379 can exist for a table even if row level security is disabled - in
380 this case, the policies will NOT be applied and the policies will
381 be ignored. See also CREATE POLICY (CREATE_POLICY(7)).
382 NO FORCE/FORCE ROW LEVEL SECURITY
384 These forms control the application of row security policies
385 belonging to the table when the user is the table owner. If
386 enabled, row level security policies will be applied when the user
387 is the table owner. If disabled (the default) then row level
388 security will not be applied when the user is the table owner. See
389 also CREATE POLICY (CREATE_POLICY(7)).
390 CLUSTER ON
392 This form selects the default index for future CLUSTER(7)
393 operations. It does not actually re-cluster the table.
394 Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
396 SET WITHOUT CLUSTER
398 This form removes the most recently used CLUSTER(7) index
399 specification from the table. This affects future cluster
400 operations that don't specify an index.
401 Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
403 SET WITHOUT OIDS
405 Backward-compatible syntax for removing the oid system column. As
406 oid system columns cannot be added anymore, this never has an
407 effect.
408 SET TABLESPACE
410 This form changes the table's tablespace to the specified
411 tablespace and moves the data file(s) associated with the table to
412 the new tablespace. Indexes on the table, if any, are not moved;
413 but they can be moved separately with additional SET TABLESPACE
414 commands. When applied to a partitioned table, nothing is moved,
415 but any partitions created afterwards with CREATE TABLE PARTITION
416 OF will use that tablespace, unless the TABLESPACE clause is used
417 to override it.
418 All tables in the current database in a tablespace can be moved by
420 using the ALL IN TABLESPACE form, which will lock all tables to be
421 moved first and then move each one. This form also supports OWNED
422 BY, which will only move tables owned by the roles specified. If
423 the NOWAIT option is specified then the command will fail if it is
424 unable to acquire all of the locks required immediately. Note that
425 system catalogs are not moved by this command; use ALTER DATABASE
426 or explicit ALTER TABLE invocations instead if desired. The
427 information_schema relations are not considered part of the system
428 catalogs and will be moved. See also CREATE TABLESPACE
429 (CREATE_TABLESPACE(7)).
430 SET { LOGGED | UNLOGGED }
432 This form changes the table from unlogged to logged or vice-versa
433 (see UNLOGGED). It cannot be applied to a temporary table.
434 SET ( storage_parameter = value [, ... ] )
436 This form changes one or more storage parameters for the table. See
437 Storage Parameters for details on the available parameters. Note
438 that the table contents will not be modified immediately by this
439 command; depending on the parameter you might need to rewrite the
440 table to get the desired effects. That can be done with VACUUM
441 FULL, CLUSTER(7) or one of the forms of ALTER TABLE that forces a
442 table rewrite. For planner related parameters, changes will take
443 effect from the next time the table is locked so currently
444 executing queries will not be affected.
445 SHARE UPDATE EXCLUSIVE lock will be taken for fillfactor, toast and
447 autovacuum storage parameters, as well as the planner parameter
448 parallel_workers.
449 RESET ( storage_parameter [, ... ] )
451 This form resets one or more storage parameters to their defaults.
452 As with SET, a table rewrite might be needed to update the table
453 entirely.
454 INHERIT parent_table
456 This form adds the target table as a new child of the specified
457 parent table. Subsequently, queries against the parent will include
458 records of the target table. To be added as a child, the target
459 table must already contain all the same columns as the parent (it
460 could have additional columns, too). The columns must have matching
461 data types, and if they have NOT NULL constraints in the parent
462 then they must also have NOT NULL constraints in the child.
463 There must also be matching child-table constraints for all CHECK
465 constraints of the parent, except those marked non-inheritable
466 (that is, created with ALTER TABLE ... ADD CONSTRAINT ... NO
467 INHERIT) in the parent, which are ignored; all child-table
468 constraints matched must not be marked non-inheritable. Currently
469 UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are not
470 considered, but this might change in the future.
471 NO INHERIT parent_table
473 This form removes the target table from the list of children of the
474 specified parent table. Queries against the parent table will no
475 longer include records drawn from the target table.
476 OF type_name
478 This form links the table to a composite type as though CREATE
479 TABLE OF had formed it. The table's list of column names and types
480 must precisely match that of the composite type. The table must not
481 inherit from any other table. These restrictions ensure that CREATE
482 TABLE OF would permit an equivalent table definition.
483 NOT OF
485 This form dissociates a typed table from its type.
486 OWNER TO
488 This form changes the owner of the table, sequence, view,
489 materialized view, or foreign table to the specified user.
490 REPLICA IDENTITY
492 This form changes the information which is written to the
493 write-ahead log to identify rows which are updated or deleted. This
494 option has no effect except when logical replication is in use.
495 DEFAULT (the default for non-system tables) records the old values
496 of the columns of the primary key, if any. USING INDEX records the
497 old values of the columns covered by the named index, which must be
498 unique, not partial, not deferrable, and include only columns
499 marked NOT NULL. FULL records the old values of all columns in the
500 row. NOTHING records no information about the old row. (This is
501 the default for system tables.) In all cases, no old values are
502 logged unless at least one of the columns that would be logged
503 differs between the old and new versions of the row.
504 RENAME
506 The RENAME forms change the name of a table (or an index, sequence,
507 view, materialized view, or foreign table), the name of an
508 individual column in a table, or the name of a constraint of the
509 table. When renaming a constraint that has an underlying index, the
510 index is renamed as well. There is no effect on the stored data.
511 SET SCHEMA
513 This form moves the table into another schema. Associated indexes,
514 constraints, and sequences owned by table columns are moved as
515 well.
516 ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec |
518 DEFAULT }
519 This form attaches an existing table (which might itself be
520 partitioned) as a partition of the target table. The table can be
521 attached as a partition for specific values using FOR VALUES or as
522 a default partition by using DEFAULT. For each index in the target
523 table, a corresponding one will be created in the attached table;
524 or, if an equivalent index already exists, it will be attached to
525 the target table's index, as if ALTER INDEX ATTACH PARTITION had
526 been executed. Note that if the existing table is a foreign table,
527 it is currently not allowed to attach the table as a partition of
528 the target table if there are UNIQUE indexes on the target table.
529 (See also CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)).)
530 A partition using FOR VALUES uses same syntax for
532 partition_bound_spec as CREATE TABLE (CREATE_TABLE(7)). The
533 partition bound specification must correspond to the partitioning
534 strategy and partition key of the target table. The table to be
535 attached must have all the same columns as the target table and no
536 more; moreover, the column types must also match. Also, it must
537 have all the NOT NULL and CHECK constraints of the target table.
538 Currently FOREIGN KEY constraints are not considered. UNIQUE and
539 PRIMARY KEY constraints from the parent table will be created in
540 the partition, if they don't already exist. If any of the CHECK
541 constraints of the table being attached is marked NO INHERIT, the
542 command will fail; such constraints must be recreated without the
543 NO INHERIT clause.
544 If the new partition is a regular table, a full table scan is
546 performed to check that existing rows in the table do not violate
547 the partition constraint. It is possible to avoid this scan by
548 adding a valid CHECK constraint to the table that allows only rows
549 satisfying the desired partition constraint before running this
550 command. The CHECK constraint will be used to determine that the
551 table need not be scanned to validate the partition constraint.
552 This does not work, however, if any of the partition keys is an
553 expression and the partition does not accept NULL values. If
554 attaching a list partition that will not accept NULL values, also
555 add NOT NULL constraint to the partition key column, unless it's an
556 expression.
557 If the new partition is a foreign table, nothing is done to verify
559 that all the rows in the foreign table obey the partition
560 constraint. (See the discussion in CREATE FOREIGN TABLE
561 (CREATE_FOREIGN_TABLE(7)) about constraints on the foreign table.)
562 When a table has a default partition, defining a new partition
564 changes the partition constraint for the default partition. The
565 default partition can't contain any rows that would need to be
566 moved to the new partition, and will be scanned to verify that none
567 are present. This scan, like the scan of the new partition, can be
568 avoided if an appropriate CHECK constraint is present. Also like
569 the scan of the new partition, it is always skipped when the
570 default partition is a foreign table.
571 Attaching a partition acquires a SHARE UPDATE EXCLUSIVE lock on the
573 parent table, in addition to ACCESS EXCLUSIVE locks on the table to
574 be attached and on the default partition (if any).
575 DETACH PARTITION partition_name
577 This form detaches specified partition of the target table. The
578 detached partition continues to exist as a standalone table, but no
579 longer has any ties to the table from which it was detached. Any
580 indexes that were attached to the target table's indexes are
581 detached.
582 All the forms of ALTER TABLE that act on a single table, except RENAME,
584 SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION can be combined into
585 a list of multiple alterations to be applied together. For example, it
586 is possible to add several columns and/or alter the type of several
587 columns in a single command. This is particularly useful with large
588 tables, since only one pass over the table need be made.
589 You must own the table to use ALTER TABLE. To change the schema or
591 tablespace of a table, you must also have CREATE privilege on the new
592 schema or tablespace. To add the table as a new child of a parent
593 table, you must own the parent table as well. Also, to attach a table
594 as a new partition of the table, you must own the table being attached.
595 To alter the owner, you must also be a direct or indirect member of the
596 new owning role, and that role must have CREATE privilege on the
597 table's schema. (These restrictions enforce that altering the owner
598 doesn't do anything you couldn't do by dropping and recreating the
599 table. However, a superuser can alter ownership of any table anyway.)
600 To add a column or alter a column type or use the OF clause, you must
601 also have USAGE privilege on the data type.
602
604 IF EXISTS
605 Do not throw an error if the table does not exist. A notice is
606 issued in this case.
607 name
609 The name (optionally schema-qualified) of an existing table to
610 alter. If ONLY is specified before the table name, only that table
611 is altered. If ONLY is not specified, the table and all its
612 descendant tables (if any) are altered. Optionally, * can be
613 specified after the table name to explicitly indicate that
614 descendant tables are included.
615 column_name
617 Name of a new or existing column.
618 new_column_name
620 New name for an existing column.
621 new_name
623 New name for the table.
624 data_type
626 Data type of the new column, or new data type for an existing
627 column.
628 table_constraint
630 New table constraint for the table.
631 constraint_name
633 Name of a new or existing constraint.
634 CASCADE
636 Automatically drop objects that depend on the dropped column or
637 constraint (for example, views referencing the column), and in turn
638 all objects that depend on those objects (see Section 5.14).
639 RESTRICT
641 Refuse to drop the column or constraint if there are any dependent
642 objects. This is the default behavior.
643 trigger_name
645 Name of a single trigger to disable or enable.
646 ALL
648 Disable or enable all triggers belonging to the table. (This
649 requires superuser privilege if any of the triggers are internally
650 generated constraint triggers such as those that are used to
651 implement foreign key constraints or deferrable uniqueness and
652 exclusion constraints.)
653 USER
655 Disable or enable all triggers belonging to the table except for
656 internally generated constraint triggers such as those that are
657 used to implement foreign key constraints or deferrable uniqueness
658 and exclusion constraints.
659 index_name
661 The name of an existing index.
662 storage_parameter
664 The name of a table storage parameter.
665 value
667 The new value for a table storage parameter. This might be a number
668 or a word depending on the parameter.
669 parent_table
671 A parent table to associate or de-associate with this table.
672 new_owner
674 The user name of the new owner of the table.
675 new_tablespace
677 The name of the tablespace to which the table will be moved.
678 new_schema
680 The name of the schema to which the table will be moved.
681 partition_name
683 The name of the table to attach as a new partition or to detach
684 from this table.
685 partition_bound_spec
687 The partition bound specification for a new partition. Refer to
688 CREATE TABLE (CREATE_TABLE(7)) for more details on the syntax of
689 the same.
690
692 The key word COLUMN is noise and can be omitted.
693 When a column is added with ADD COLUMN and a non-volatile DEFAULT is
695 specified, the default is evaluated at the time of the statement and
696 the result stored in the table's metadata. That value will be used for
697 the column for all existing rows. If no DEFAULT is specified, NULL is
698 used. In neither case is a rewrite of the table required.
699 Adding a column with a volatile DEFAULT or changing the type of an
701 existing column will require the entire table and its indexes to be
702 rewritten. As an exception, when changing the type of an existing
703 column, if the USING clause does not change the column contents and the
704 old type is either binary coercible to the new type or an unconstrained
705 domain over the new type, a table rewrite is not needed; but any
706 indexes on the affected columns must still be rebuilt. Table and/or
707 index rebuilds may take a significant amount of time for a large table;
708 and will temporarily require as much as double the disk space.
709 Adding a CHECK or NOT NULL constraint requires scanning the table to
711 verify that existing rows meet the constraint, but does not require a
712 table rewrite.
713 Similarly, when attaching a new partition it may be scanned to verify
715 that existing rows meet the partition constraint.
716 The main reason for providing the option to specify multiple changes in
718 a single ALTER TABLE is that multiple table scans or rewrites can
719 thereby be combined into a single pass over the table.
720 Scanning a large table to verify a new foreign key or check constraint
722 can take a long time, and other updates to the table are locked out
723 until the ALTER TABLE ADD CONSTRAINT command is committed. The main
724 purpose of the NOT VALID constraint option is to reduce the impact of
725 adding a constraint on concurrent updates. With NOT VALID, the ADD
726 CONSTRAINT command does not scan the table and can be committed
727 immediately. After that, a VALIDATE CONSTRAINT command can be issued to
728 verify that existing rows satisfy the constraint. The validation step
729 does not need to lock out concurrent updates, since it knows that other
730 transactions will be enforcing the constraint for rows that they insert
731 or update; only pre-existing rows need to be checked. Hence, validation
732 acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered.
733 (If the constraint is a foreign key then a ROW SHARE lock is also
734 required on the table referenced by the constraint.) In addition to
735 improving concurrency, it can be useful to use NOT VALID and VALIDATE
736 CONSTRAINT in cases where the table is known to contain pre-existing
737 violations. Once the constraint is in place, no new violations can be
738 inserted, and the existing problems can be corrected at leisure until
739 VALIDATE CONSTRAINT finally succeeds.
740 The DROP COLUMN form does not physically remove the column, but simply
742 makes it invisible to SQL operations. Subsequent insert and update
743 operations in the table will store a null value for the column. Thus,
744 dropping a column is quick but it will not immediately reduce the
745 on-disk size of your table, as the space occupied by the dropped column
746 is not reclaimed. The space will be reclaimed over time as existing
747 rows are updated.
748 To force immediate reclamation of space occupied by a dropped column,
750 you can execute one of the forms of ALTER TABLE that performs a rewrite
751 of the whole table. This results in reconstructing each row with the
752 dropped column replaced by a null value.
753 The rewriting forms of ALTER TABLE are not MVCC-safe. After a table
755 rewrite, the table will appear empty to concurrent transactions, if
756 they are using a snapshot taken before the rewrite occurred. See
757 Section 13.5 for more details.
758 The USING option of SET DATA TYPE can actually specify any expression
760 involving the old values of the row; that is, it can refer to other
761 columns as well as the one being converted. This allows very general
762 conversions to be done with the SET DATA TYPE syntax. Because of this
763 flexibility, the USING expression is not applied to the column's
764 default value (if any); the result might not be a constant expression
765 as required for a default. This means that when there is no implicit or
766 assignment cast from old to new type, SET DATA TYPE might fail to
767 convert the default even though a USING clause is supplied. In such
768 cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and
769 then use SET DEFAULT to add a suitable new default. Similar
770 considerations apply to indexes and constraints involving the column.
771 If a table has any descendant tables, it is not permitted to add,
773 rename, or change the type of a column in the parent table without
774 doing the same to the descendants. This ensures that the descendants
775 always have columns matching the parent. Similarly, a CHECK constraint
776 cannot be renamed in the parent without also renaming it in all
777 descendants, so that CHECK constraints also match between the parent
778 and its descendants. (That restriction does not apply to index-based
779 constraints, however.) Also, because selecting from the parent also
780 selects from its descendants, a constraint on the parent cannot be
781 marked valid unless it is also marked valid for those descendants. In
782 all of these cases, ALTER TABLE ONLY will be rejected.
783 A recursive DROP COLUMN operation will remove a descendant table's
785 column only if the descendant does not inherit that column from any
786 other parents and never had an independent definition of the column. A
787 nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never
788 removes any descendant columns, but instead marks them as independently
789 defined rather than inherited. A nonrecursive DROP COLUMN command will
790 fail for a partitioned table, because all partitions of a table must
791 have the same columns as the partitioning root.
792 The actions for identity columns (ADD GENERATED, SET etc., DROP
794 IDENTITY), as well as the actions TRIGGER, CLUSTER, OWNER, and
795 TABLESPACE never recurse to descendant tables; that is, they always act
796 as though ONLY were specified. Adding a constraint recurses only for
797 CHECK constraints that are not marked NO INHERIT.
798 Changing any part of a system catalog table is not permitted.
800 Refer to CREATE TABLE (CREATE_TABLE(7)) for a further description of
802 valid parameters. Chapter 5 has further information on inheritance.
803
805 To add a column of type varchar to a table:
806 ALTER TABLE distributors ADD COLUMN address varchar(30);
808 To drop a column from a table:
810 ALTER TABLE distributors DROP COLUMN address RESTRICT;
812 To change the types of two existing columns in one operation:
814 ALTER TABLE distributors
816 ALTER COLUMN address TYPE varchar(80),
817 ALTER COLUMN name TYPE varchar(100);
818 To change an integer column containing Unix timestamps to timestamp
820 with time zone via a USING clause:
821 ALTER TABLE foo
823 ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
824 USING
825 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
826 The same, when the column has a default expression that won't
828 automatically cast to the new data type:
829 ALTER TABLE foo
831 ALTER COLUMN foo_timestamp DROP DEFAULT,
832 ALTER COLUMN foo_timestamp TYPE timestamp with time zone
833 USING
834 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
835 ALTER COLUMN foo_timestamp SET DEFAULT now();
836 To rename an existing column:
838 ALTER TABLE distributors RENAME COLUMN address TO city;
840 To rename an existing table:
842 ALTER TABLE distributors RENAME TO suppliers;
844 To rename an existing constraint:
846 ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
848 To add a not-null constraint to a column:
850 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
852 To remove a not-null constraint from a column:
854 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
856 To add a check constraint to a table and all its children:
858 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
860 To add a check constraint only to a table and not to its children:
862 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
864 (The check constraint will not be inherited by future children,
866 either.)
867 To remove a check constraint from a table and all its children:
869 ALTER TABLE distributors DROP CONSTRAINT zipchk;
871 To remove a check constraint from one table only:
873 ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
875 (The check constraint remains in place for any child tables.)
877 To add a foreign key constraint to a table:
879 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
881 To add a foreign key constraint to a table with the least impact on
883 other work:
884 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
886 ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
887 To add a (multicolumn) unique constraint to a table:
889 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
891 To add an automatically named primary key constraint to a table, noting
893 that a table can only ever have one primary key:
894 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
896 To move a table to a different tablespace:
898 ALTER TABLE distributors SET TABLESPACE fasttablespace;
900 To move a table to a different schema:
902 ALTER TABLE myschema.distributors SET SCHEMA yourschema;
904 To recreate a primary key constraint, without blocking updates while
906 the index is rebuilt:
907 CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
909 ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
910 ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
911 To attach a partition to a range-partitioned table:
913 ALTER TABLE measurement
915 ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
916 To attach a partition to a list-partitioned table:
918 ALTER TABLE cities
920 ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
921 To attach a partition to a hash-partitioned table:
923 ALTER TABLE orders
925 ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
926 To attach a default partition to a partitioned table:
928 ALTER TABLE cities
930 ATTACH PARTITION cities_partdef DEFAULT;
931 To detach a partition from a partitioned table:
933 ALTER TABLE measurement
935 DETACH PARTITION measurement_y2015m12;
936
938 The forms ADD (without USING INDEX), DROP [COLUMN], DROP IDENTITY,
939 RESTART, SET DEFAULT, SET DATA TYPE (without USING), SET GENERATED, and
940 SET sequence_option conform with the SQL standard. The other forms are
941 PostgreSQL extensions of the SQL standard. Also, the ability to specify
942 more than one manipulation in a single ALTER TABLE command is an
943 extension.
944 ALTER TABLE DROP COLUMN can be used to drop the only column of a table,
946 leaving a zero-column table. This is an extension of SQL, which
947 disallows zero-column tables.
948
950 CREATE TABLE (CREATE_TABLE(7))
951PostgreSQL 12.2 2020 ALTER TABLE(7)