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