1ALTER TABLE(7) PostgreSQL 12.6 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 acquired unless
125 explicitly noted. When multiple subcommands are given, the lock
126 acquired will be the strictest one required by 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
251 constraint syntax as CREATE TABLE (CREATE_TABLE(7)), plus the
252 option NOT VALID, which is currently only allowed for foreign key
253 and CHECK constraints.
254 Normally, this form will cause a scan of the table to verify that
256 all existing rows in the table satisfy the new constraint. But if
257 the NOT VALID option is used, this potentially-lengthy scan is
258 skipped. The constraint will still be enforced against subsequent
259 inserts or updates (that is, they'll fail unless there is a
260 matching row in the referenced table, in the case of foreign keys,
261 or they'll fail unless the new row matches the specified check
262 condition). But the database will not assume that the constraint
263 holds for all rows in the table, until it is validated by using the
264 VALIDATE CONSTRAINT option. See NOTES below for more information
265 about using the NOT VALID option.
266 Although most forms of ADD table_constraint require an ACCESS
268 EXCLUSIVE lock, ADD FOREIGN KEY requires only a SHARE ROW EXCLUSIVE
269 lock. Note that ADD FOREIGN KEY also acquires a SHARE ROW EXCLUSIVE
270 lock on the referenced table, in addition to the lock on the table
271 on which the constraint is declared.
272 Additional restrictions apply when unique or primary key
274 constraints are added to partitioned tables; see CREATE TABLE
275 (CREATE_TABLE(7)). Also, foreign key constraints on partitioned
276 tables may not be declared NOT VALID at present.
277 ADD table_constraint_using_index
279 This form adds a new PRIMARY KEY or UNIQUE constraint to a table
280 based on an existing unique index. All the columns of the index
281 will be included in the constraint.
282 The index cannot have expression columns nor be a partial index.
284 Also, it must be a b-tree index with default sort ordering. These
285 restrictions ensure that the index is equivalent to one that would
286 be built by a regular ADD PRIMARY KEY or ADD UNIQUE command.
287 If PRIMARY KEY is specified, and the index's columns are not
289 already marked NOT NULL, then this command will attempt to do ALTER
290 COLUMN SET NOT NULL against each such column. That requires a full
291 table scan to verify the column(s) contain no nulls. In all other
292 cases, this is a fast operation.
293 If a constraint name is provided then the index will be renamed to
295 match the constraint name. Otherwise the constraint will be named
296 the same as the index.
297 After this command is executed, the index is “owned” by the
299 constraint, in the same way as if the index had been built by a
300 regular ADD PRIMARY KEY or ADD UNIQUE command. In particular,
301 dropping the constraint will make the index disappear too.
302 This form is not currently supported on partitioned tables.
304 Note
306 Adding a constraint using an existing index can be helpful in
307 situations where a new constraint needs to be added without
308 blocking table updates for a long time. To do that, create the
309 index using CREATE INDEX CONCURRENTLY, and then install it as
310 an official constraint using this syntax. See the example
311 below.
312 ALTER CONSTRAINT
314 This form alters the attributes of a constraint that was previously
315 created. Currently only foreign key constraints may be altered.
316 VALIDATE CONSTRAINT
318 This form validates a foreign key or check constraint that was
319 previously created as NOT VALID, by scanning the table to ensure
320 there are no rows for which the constraint is not satisfied.
321 Nothing happens if the constraint is already marked valid. (See
322 NOTES below for an explanation of the usefulness of this command.)
323 DROP CONSTRAINT [ IF EXISTS ]
325 This form drops the specified constraint on a table, along with any
326 index underlying the constraint. If IF EXISTS is specified and the
327 constraint does not exist, no error is thrown. In this case a
328 notice is issued instead.
329 DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
331 These forms configure the firing of trigger(s) belonging to the
332 table. A disabled trigger is still known to the system, but is not
333 executed when its triggering event occurs. For a deferred trigger,
334 the enable status is checked when the event occurs, not when the
335 trigger function is actually executed. One can disable or enable a
336 single trigger specified by name, or all triggers on the table, or
337 only user triggers (this option excludes internally generated
338 constraint triggers such as those that are used to implement
339 foreign key constraints or deferrable uniqueness and exclusion
340 constraints). Disabling or enabling internally generated constraint
341 triggers requires superuser privileges; it should be done with
342 caution since of course the integrity of the constraint cannot be
343 guaranteed if the triggers are not executed.
344 The trigger firing mechanism is also affected by the configuration
346 variable session_replication_role. Simply enabled triggers (the
347 default) will fire when the replication role is “origin” (the
348 default) or “local”. Triggers configured as ENABLE REPLICA will
349 only fire if the session is in “replica” mode, and triggers
350 configured as ENABLE ALWAYS will fire regardless of the current
351 replication role.
352 The effect of this mechanism is that in the default configuration,
354 triggers do not fire on replicas. This is useful because if a
355 trigger is used on the origin to propagate data between tables,
356 then the replication system will also replicate the propagated
357 data, and the trigger should not fire a second time on the replica,
358 because that would lead to duplication. However, if a trigger is
359 used for another purpose such as creating external alerts, then it
360 might be appropriate to set it to ENABLE ALWAYS so that it is also
361 fired on replicas.
362 This command acquires a SHARE ROW EXCLUSIVE lock.
364 DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
366 These forms configure the firing of rewrite rules belonging to the
367 table. A disabled rule is still known to the system, but is not
368 applied during query rewriting. The semantics are as for
369 disabled/enabled triggers. This configuration is ignored for ON
370 SELECT rules, which are always applied in order to keep views
371 working even if the current session is in a non-default replication
372 role.
373 The rule firing mechanism is also affected by the configuration
375 variable session_replication_role, analogous to triggers as
376 described above.
377 DISABLE/ENABLE ROW LEVEL SECURITY
379 These forms control the application of row security policies
380 belonging to the table. If enabled and no policies exist for the
381 table, then a default-deny policy is applied. Note that policies
382 can exist for a table even if row level security is disabled - in
383 this case, the policies will NOT be applied and the policies will
384 be ignored. See also CREATE POLICY (CREATE_POLICY(7)).
385 NO FORCE/FORCE ROW LEVEL SECURITY
387 These forms control the application of row security policies
388 belonging to the table when the user is the table owner. If
389 enabled, row level security policies will be applied when the user
390 is the table owner. If disabled (the default) then row level
391 security will not be applied when the user is the table owner. See
392 also CREATE POLICY (CREATE_POLICY(7)).
393 CLUSTER ON
395 This form selects the default index for future CLUSTER(7)
396 operations. It does not actually re-cluster the table.
397 Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
399 SET WITHOUT CLUSTER
401 This form removes the most recently used CLUSTER(7) index
402 specification from the table. This affects future cluster
403 operations that don't specify an index.
404 Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
406 SET WITHOUT OIDS
408 Backward-compatible syntax for removing the oid system column. As
409 oid system columns cannot be added anymore, this never has an
410 effect.
411 SET TABLESPACE
413 This form changes the table's tablespace to the specified
414 tablespace and moves the data file(s) associated with the table to
415 the new tablespace. Indexes on the table, if any, are not moved;
416 but they can be moved separately with additional SET TABLESPACE
417 commands. When applied to a partitioned table, nothing is moved,
418 but any partitions created afterwards with CREATE TABLE PARTITION
419 OF will use that tablespace, unless the TABLESPACE clause is used
420 to override it.
421 All tables in the current database in a tablespace can be moved by
423 using the ALL IN TABLESPACE form, which will lock all tables to be
424 moved first and then move each one. This form also supports OWNED
425 BY, which will only move tables owned by the roles specified. If
426 the NOWAIT option is specified then the command will fail if it is
427 unable to acquire all of the locks required immediately. Note that
428 system catalogs are not moved by this command; use ALTER DATABASE
429 or explicit ALTER TABLE invocations instead if desired. The
430 information_schema relations are not considered part of the system
431 catalogs and will be moved. See also CREATE TABLESPACE
432 (CREATE_TABLESPACE(7)).
433 SET { LOGGED | UNLOGGED }
435 This form changes the table from unlogged to logged or vice-versa
436 (see UNLOGGED). It cannot be applied to a temporary table.
437 SET ( storage_parameter [= value] [, ... ] )
439 This form changes one or more storage parameters for the table. See
440 Storage Parameters for details on the available parameters. Note
441 that the table contents will not be modified immediately by this
442 command; depending on the parameter you might need to rewrite the
443 table to get the desired effects. That can be done with VACUUM
444 FULL, CLUSTER(7) or one of the forms of ALTER TABLE that forces a
445 table rewrite. For planner related parameters, changes will take
446 effect from the next time the table is locked so currently
447 executing queries will not be affected.
448 SHARE UPDATE EXCLUSIVE lock will be taken for fillfactor, toast and
450 autovacuum storage parameters, as well as the planner parameter
451 parallel_workers.
452 RESET ( storage_parameter [, ... ] )
454 This form resets one or more storage parameters to their defaults.
455 As with SET, a table rewrite might be needed to update the table
456 entirely.
457 INHERIT parent_table
459 This form adds the target table as a new child of the specified
460 parent table. Subsequently, queries against the parent will include
461 records of the target table. To be added as a child, the target
462 table must already contain all the same columns as the parent (it
463 could have additional columns, too). The columns must have matching
464 data types, and if they have NOT NULL constraints in the parent
465 then they must also have NOT NULL constraints in the child.
466 There must also be matching child-table constraints for all CHECK
468 constraints of the parent, except those marked non-inheritable
469 (that is, created with ALTER TABLE ... ADD CONSTRAINT ... NO
470 INHERIT) in the parent, which are ignored; all child-table
471 constraints matched must not be marked non-inheritable. Currently
472 UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are not
473 considered, but this might change in the future.
474 NO INHERIT parent_table
476 This form removes the target table from the list of children of the
477 specified parent table. Queries against the parent table will no
478 longer include records drawn from the target table.
479 OF type_name
481 This form links the table to a composite type as though CREATE
482 TABLE OF had formed it. The table's list of column names and types
483 must precisely match that of the composite type. The table must not
484 inherit from any other table. These restrictions ensure that CREATE
485 TABLE OF would permit an equivalent table definition.
486 NOT OF
488 This form dissociates a typed table from its type.
489 OWNER TO
491 This form changes the owner of the table, sequence, view,
492 materialized view, or foreign table to the specified user.
493 REPLICA IDENTITY
495 This form changes the information which is written to the
496 write-ahead log to identify rows which are updated or deleted. This
497 option has no effect except when logical replication is in use.
498 DEFAULT (the default for non-system tables) records the old values
499 of the columns of the primary key, if any. USING INDEX records the
500 old values of the columns covered by the named index, which must be
501 unique, not partial, not deferrable, and include only columns
502 marked NOT NULL. FULL records the old values of all columns in the
503 row. NOTHING records no information about the old row. (This is
504 the default for system tables.) In all cases, no old values are
505 logged unless at least one of the columns that would be logged
506 differs between the old and new versions of the row.
507 RENAME
509 The RENAME forms change the name of a table (or an index, sequence,
510 view, materialized view, or foreign table), the name of an
511 individual column in a table, or the name of a constraint of the
512 table. When renaming a constraint that has an underlying index, the
513 index is renamed as well. There is no effect on the stored data.
514 SET SCHEMA
516 This form moves the table into another schema. Associated indexes,
517 constraints, and sequences owned by table columns are moved as
518 well.
519 ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec |
521 DEFAULT }
522 This form attaches an existing table (which might itself be
523 partitioned) as a partition of the target table. The table can be
524 attached as a partition for specific values using FOR VALUES or as
525 a default partition by using DEFAULT. For each index in the target
526 table, a corresponding one will be created in the attached table;
527 or, if an equivalent index already exists, it will be attached to
528 the target table's index, as if ALTER INDEX ATTACH PARTITION had
529 been executed. Note that if the existing table is a foreign table,
530 it is currently not allowed to attach the table as a partition of
531 the target table if there are UNIQUE indexes on the target table.
532 (See also CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)).) For each
533 user-defined row-level trigger that exists in the target table, a
534 corresponding one is created in the attached table.
535 A partition using FOR VALUES uses same syntax for
537 partition_bound_spec as CREATE TABLE (CREATE_TABLE(7)). The
538 partition bound specification must correspond to the partitioning
539 strategy and partition key of the target table. The table to be
540 attached must have all the same columns as the target table and no
541 more; moreover, the column types must also match. Also, it must
542 have all the NOT NULL and CHECK constraints of the target table.
543 Currently FOREIGN KEY constraints are not considered. UNIQUE and
544 PRIMARY KEY constraints from the parent table will be created in
545 the partition, if they don't already exist. If any of the CHECK
546 constraints of the table being attached are marked NO INHERIT, the
547 command will fail; such constraints must be recreated without the
548 NO INHERIT clause.
549 If the new partition is a regular table, a full table scan is
551 performed to check that existing rows in the table do not violate
552 the partition constraint. It is possible to avoid this scan by
553 adding a valid CHECK constraint to the table that allows only rows
554 satisfying the desired partition constraint before running this
555 command. The CHECK constraint will be used to determine that the
556 table need not be scanned to validate the partition constraint.
557 This does not work, however, if any of the partition keys is an
558 expression and the partition does not accept NULL values. If
559 attaching a list partition that will not accept NULL values, also
560 add NOT NULL constraint to the partition key column, unless it's an
561 expression.
562 If the new partition is a foreign table, nothing is done to verify
564 that all the rows in the foreign table obey the partition
565 constraint. (See the discussion in CREATE FOREIGN TABLE
566 (CREATE_FOREIGN_TABLE(7)) about constraints on the foreign table.)
567 When a table has a default partition, defining a new partition
569 changes the partition constraint for the default partition. The
570 default partition can't contain any rows that would need to be
571 moved to the new partition, and will be scanned to verify that none
572 are present. This scan, like the scan of the new partition, can be
573 avoided if an appropriate CHECK constraint is present. Also like
574 the scan of the new partition, it is always skipped when the
575 default partition is a foreign table.
576 Attaching a partition acquires a SHARE UPDATE EXCLUSIVE lock on the
578 parent table, in addition to ACCESS EXCLUSIVE locks on the table to
579 be attached and on the default partition (if any).
580 DETACH PARTITION partition_name
582 This form detaches the specified partition of the target table. The
583 detached partition continues to exist as a standalone table, but no
584 longer has any ties to the table from which it was detached. Any
585 indexes that were attached to the target table's indexes are
586 detached. Any triggers that were created as clones of those in the
587 target table are removed.
588 All the forms of ALTER TABLE that act on a single table, except RENAME,
590 SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION can be combined into
591 a list of multiple alterations to be applied together. For example, it
592 is possible to add several columns and/or alter the type of several
593 columns in a single command. This is particularly useful with large
594 tables, since only one pass over the table need be made.
595 You must own the table to use ALTER TABLE. To change the schema or
597 tablespace of a table, you must also have CREATE privilege on the new
598 schema or tablespace. To add the table as a new child of a parent
599 table, you must own the parent table as well. Also, to attach a table
600 as a new partition of the table, you must own the table being attached.
601 To alter the owner, you must also be a direct or indirect member of the
602 new owning role, and that role must have CREATE privilege on the
603 table's schema. (These restrictions enforce that altering the owner
604 doesn't do anything you couldn't do by dropping and recreating the
605 table. However, a superuser can alter ownership of any table anyway.)
606 To add a column or alter a column type or use the OF clause, you must
607 also have USAGE privilege on the data type.
608
610 IF EXISTS
611 Do not throw an error if the table does not exist. A notice is
612 issued in this case.
613 name
615 The name (optionally schema-qualified) of an existing table to
616 alter. If ONLY is specified before the table name, only that table
617 is altered. If ONLY is not specified, the table and all its
618 descendant tables (if any) are altered. Optionally, * can be
619 specified after the table name to explicitly indicate that
620 descendant tables are included.
621 column_name
623 Name of a new or existing column.
624 new_column_name
626 New name for an existing column.
627 new_name
629 New name for the table.
630 data_type
632 Data type of the new column, or new data type for an existing
633 column.
634 table_constraint
636 New table constraint for the table.
637 constraint_name
639 Name of a new or existing constraint.
640 CASCADE
642 Automatically drop objects that depend on the dropped column or
643 constraint (for example, views referencing the column), and in turn
644 all objects that depend on those objects (see Section 5.14).
645 RESTRICT
647 Refuse to drop the column or constraint if there are any dependent
648 objects. This is the default behavior.
649 trigger_name
651 Name of a single trigger to disable or enable.
652 ALL
654 Disable or enable all triggers belonging to the table. (This
655 requires superuser privilege if any of the triggers are internally
656 generated constraint triggers such as those that are used to
657 implement foreign key constraints or deferrable uniqueness and
658 exclusion constraints.)
659 USER
661 Disable or enable all triggers belonging to the table except for
662 internally generated constraint triggers such as those that are
663 used to implement foreign key constraints or deferrable uniqueness
664 and exclusion constraints.
665 index_name
667 The name of an existing index.
668 storage_parameter
670 The name of a table storage parameter.
671 value
673 The new value for a table storage parameter. This might be a number
674 or a word depending on the parameter.
675 parent_table
677 A parent table to associate or de-associate with this table.
678 new_owner
680 The user name of the new owner of the table.
681 new_tablespace
683 The name of the tablespace to which the table will be moved.
684 new_schema
686 The name of the schema to which the table will be moved.
687 partition_name
689 The name of the table to attach as a new partition or to detach
690 from this table.
691 partition_bound_spec
693 The partition bound specification for a new partition. Refer to
694 CREATE TABLE (CREATE_TABLE(7)) for more details on the syntax of
695 the same.
696
698 The key word COLUMN is noise and can be omitted.
699 When a column is added with ADD COLUMN and a non-volatile DEFAULT is
701 specified, the default is evaluated at the time of the statement and
702 the result stored in the table's metadata. That value will be used for
703 the column for all existing rows. If no DEFAULT is specified, NULL is
704 used. In neither case is a rewrite of the table required.
705 Adding a column with a volatile DEFAULT or changing the type of an
707 existing column will require the entire table and its indexes to be
708 rewritten. As an exception, when changing the type of an existing
709 column, if the USING clause does not change the column contents and the
710 old type is either binary coercible to the new type or an unconstrained
711 domain over the new type, a table rewrite is not needed; but any
712 indexes on the affected columns must still be rebuilt. Table and/or
713 index rebuilds may take a significant amount of time for a large table;
714 and will temporarily require as much as double the disk space.
715 Adding a CHECK or NOT NULL constraint requires scanning the table to
717 verify that existing rows meet the constraint, but does not require a
718 table rewrite.
719 Similarly, when attaching a new partition it may be scanned to verify
721 that existing rows meet the partition constraint.
722 The main reason for providing the option to specify multiple changes in
724 a single ALTER TABLE is that multiple table scans or rewrites can
725 thereby be combined into a single pass over the table.
726 Scanning a large table to verify a new foreign key or check constraint
728 can take a long time, and other updates to the table are locked out
729 until the ALTER TABLE ADD CONSTRAINT command is committed. The main
730 purpose of the NOT VALID constraint option is to reduce the impact of
731 adding a constraint on concurrent updates. With NOT VALID, the ADD
732 CONSTRAINT command does not scan the table and can be committed
733 immediately. After that, a VALIDATE CONSTRAINT command can be issued to
734 verify that existing rows satisfy the constraint. The validation step
735 does not need to lock out concurrent updates, since it knows that other
736 transactions will be enforcing the constraint for rows that they insert
737 or update; only pre-existing rows need to be checked. Hence, validation
738 acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered.
739 (If the constraint is a foreign key then a ROW SHARE lock is also
740 required on the table referenced by the constraint.) In addition to
741 improving concurrency, it can be useful to use NOT VALID and VALIDATE
742 CONSTRAINT in cases where the table is known to contain pre-existing
743 violations. Once the constraint is in place, no new violations can be
744 inserted, and the existing problems can be corrected at leisure until
745 VALIDATE CONSTRAINT finally succeeds.
746 The DROP COLUMN form does not physically remove the column, but simply
748 makes it invisible to SQL operations. Subsequent insert and update
749 operations in the table will store a null value for the column. Thus,
750 dropping a column is quick but it will not immediately reduce the
751 on-disk size of your table, as the space occupied by the dropped column
752 is not reclaimed. The space will be reclaimed over time as existing
753 rows are updated.
754 To force immediate reclamation of space occupied by a dropped column,
756 you can execute one of the forms of ALTER TABLE that performs a rewrite
757 of the whole table. This results in reconstructing each row with the
758 dropped column replaced by a null value.
759 The rewriting forms of ALTER TABLE are not MVCC-safe. After a table
761 rewrite, the table will appear empty to concurrent transactions, if
762 they are using a snapshot taken before the rewrite occurred. See
763 Section 13.5 for more details.
764 The USING option of SET DATA TYPE can actually specify any expression
766 involving the old values of the row; that is, it can refer to other
767 columns as well as the one being converted. This allows very general
768 conversions to be done with the SET DATA TYPE syntax. Because of this
769 flexibility, the USING expression is not applied to the column's
770 default value (if any); the result might not be a constant expression
771 as required for a default. This means that when there is no implicit or
772 assignment cast from old to new type, SET DATA TYPE might fail to
773 convert the default even though a USING clause is supplied. In such
774 cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and
775 then use SET DEFAULT to add a suitable new default. Similar
776 considerations apply to indexes and constraints involving the column.
777 If a table has any descendant tables, it is not permitted to add,
779 rename, or change the type of a column in the parent table without
780 doing the same to the descendants. This ensures that the descendants
781 always have columns matching the parent. Similarly, a CHECK constraint
782 cannot be renamed in the parent without also renaming it in all
783 descendants, so that CHECK constraints also match between the parent
784 and its descendants. (That restriction does not apply to index-based
785 constraints, however.) Also, because selecting from the parent also
786 selects from its descendants, a constraint on the parent cannot be
787 marked valid unless it is also marked valid for those descendants. In
788 all of these cases, ALTER TABLE ONLY will be rejected.
789 A recursive DROP COLUMN operation will remove a descendant table's
791 column only if the descendant does not inherit that column from any
792 other parents and never had an independent definition of the column. A
793 nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never
794 removes any descendant columns, but instead marks them as independently
795 defined rather than inherited. A nonrecursive DROP COLUMN command will
796 fail for a partitioned table, because all partitions of a table must
797 have the same columns as the partitioning root.
798 The actions for identity columns (ADD GENERATED, SET etc., DROP
800 IDENTITY), as well as the actions TRIGGER, CLUSTER, OWNER, and
801 TABLESPACE never recurse to descendant tables; that is, they always act
802 as though ONLY were specified. Adding a constraint recurses only for
803 CHECK constraints that are not marked NO INHERIT.
804 Changing any part of a system catalog table is not permitted.
806 Refer to CREATE TABLE (CREATE_TABLE(7)) for a further description of
808 valid parameters. Chapter 5 has further information on inheritance.
809
811 To add a column of type varchar to a table:
812 ALTER TABLE distributors ADD COLUMN address varchar(30);
814 To drop a column from a table:
816 ALTER TABLE distributors DROP COLUMN address RESTRICT;
818 To change the types of two existing columns in one operation:
820 ALTER TABLE distributors
822 ALTER COLUMN address TYPE varchar(80),
823 ALTER COLUMN name TYPE varchar(100);
824 To change an integer column containing Unix timestamps to timestamp
826 with time zone via a USING clause:
827 ALTER TABLE foo
829 ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
830 USING
831 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
832 The same, when the column has a default expression that won't
834 automatically cast to the new data type:
835 ALTER TABLE foo
837 ALTER COLUMN foo_timestamp DROP DEFAULT,
838 ALTER COLUMN foo_timestamp TYPE timestamp with time zone
839 USING
840 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
841 ALTER COLUMN foo_timestamp SET DEFAULT now();
842 To rename an existing column:
844 ALTER TABLE distributors RENAME COLUMN address TO city;
846 To rename an existing table:
848 ALTER TABLE distributors RENAME TO suppliers;
850 To rename an existing constraint:
852 ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
854 To add a not-null constraint to a column:
856 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
858 To remove a not-null constraint from a column:
860 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
862 To add a check constraint to a table and all its children:
864 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
866 To add a check constraint only to a table and not to its children:
868 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
870 (The check constraint will not be inherited by future children,
872 either.)
873 To remove a check constraint from a table and all its children:
875 ALTER TABLE distributors DROP CONSTRAINT zipchk;
877 To remove a check constraint from one table only:
879 ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
881 (The check constraint remains in place for any child tables.)
883 To add a foreign key constraint to a table:
885 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
887 To add a foreign key constraint to a table with the least impact on
889 other work:
890 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
892 ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
893 To add a (multicolumn) unique constraint to a table:
895 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
897 To add an automatically named primary key constraint to a table, noting
899 that a table can only ever have one primary key:
900 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
902 To move a table to a different tablespace:
904 ALTER TABLE distributors SET TABLESPACE fasttablespace;
906 To move a table to a different schema:
908 ALTER TABLE myschema.distributors SET SCHEMA yourschema;
910 To recreate a primary key constraint, without blocking updates while
912 the index is rebuilt:
913 CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
915 ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
916 ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
917 To attach a partition to a range-partitioned table:
919 ALTER TABLE measurement
921 ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
922 To attach a partition to a list-partitioned table:
924 ALTER TABLE cities
926 ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
927 To attach a partition to a hash-partitioned table:
929 ALTER TABLE orders
931 ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
932 To attach a default partition to a partitioned table:
934 ALTER TABLE cities
936 ATTACH PARTITION cities_partdef DEFAULT;
937 To detach a partition from a partitioned table:
939 ALTER TABLE measurement
941 DETACH PARTITION measurement_y2015m12;
942
944 The forms ADD (without USING INDEX), DROP [COLUMN], DROP IDENTITY,
945 RESTART, SET DEFAULT, SET DATA TYPE (without USING), SET GENERATED, and
946 SET sequence_option conform with the SQL standard. The other forms are
947 PostgreSQL extensions of the SQL standard. Also, the ability to specify
948 more than one manipulation in a single ALTER TABLE command is an
949 extension.
950 ALTER TABLE DROP COLUMN can be used to drop the only column of a table,
952 leaving a zero-column table. This is an extension of SQL, which
953 disallows zero-column tables.
954
956 CREATE TABLE (CREATE_TABLE(7))
957PostgreSQL 12.6 2021 ALTER TABLE(7)