1ALTER TABLE(7) PostgreSQL 13.3 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 68.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 ACCESS EXCLUSIVE locks on the table to
595 be attached and on the default partition (if any).
596 DETACH PARTITION partition_name
598 This form detaches the specified partition of the target table. The
599 detached partition continues to exist as a standalone table, but no
600 longer has any ties to the table from which it was detached. Any
601 indexes that were attached to the target table's indexes are
602 detached. Any triggers that were created as clones of those in the
603 target table are removed. SHARE lock is obtained on any tables
604 that reference this partitioned table in foreign key constraints.
605 All the forms of ALTER TABLE that act on a single table, except RENAME,
607 SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION can be combined into
608 a list of multiple alterations to be applied together. For example, it
609 is possible to add several columns and/or alter the type of several
610 columns in a single command. This is particularly useful with large
611 tables, since only one pass over the table need be made.
612 You must own the table to use ALTER TABLE. To change the schema or
614 tablespace of a table, you must also have CREATE privilege on the new
615 schema or tablespace. To add the table as a new child of a parent
616 table, you must own the parent table as well. Also, to attach a table
617 as a new partition of the table, you must own the table being attached.
618 To alter the owner, you must also be a direct or indirect member of the
619 new owning role, and that role must have CREATE privilege on the
620 table's schema. (These restrictions enforce that altering the owner
621 doesn't do anything you couldn't do by dropping and recreating the
622 table. However, a superuser can alter ownership of any table anyway.)
623 To add a column or alter a column type or use the OF clause, you must
624 also have USAGE privilege on the data type.
625
627 IF EXISTS
628 Do not throw an error if the table does not exist. A notice is
629 issued in this case.
630 name
632 The name (optionally schema-qualified) of an existing table to
633 alter. If ONLY is specified before the table name, only that table
634 is altered. If ONLY is not specified, the table and all its
635 descendant tables (if any) are altered. Optionally, * can be
636 specified after the table name to explicitly indicate that
637 descendant tables are included.
638 column_name
640 Name of a new or existing column.
641 new_column_name
643 New name for an existing column.
644 new_name
646 New name for the table.
647 data_type
649 Data type of the new column, or new data type for an existing
650 column.
651 table_constraint
653 New table constraint for the table.
654 constraint_name
656 Name of a new or existing constraint.
657 CASCADE
659 Automatically drop objects that depend on the dropped column or
660 constraint (for example, views referencing the column), and in turn
661 all objects that depend on those objects (see Section 5.14).
662 RESTRICT
664 Refuse to drop the column or constraint if there are any dependent
665 objects. This is the default behavior.
666 trigger_name
668 Name of a single trigger to disable or enable.
669 ALL
671 Disable or enable all triggers belonging to the table. (This
672 requires superuser privilege if any of the triggers are internally
673 generated constraint triggers such as those that are used to
674 implement foreign key constraints or deferrable uniqueness and
675 exclusion constraints.)
676 USER
678 Disable or enable all triggers belonging to the table except for
679 internally generated constraint triggers such as those that are
680 used to implement foreign key constraints or deferrable uniqueness
681 and exclusion constraints.
682 index_name
684 The name of an existing index.
685 storage_parameter
687 The name of a table storage parameter.
688 value
690 The new value for a table storage parameter. This might be a number
691 or a word depending on the parameter.
692 parent_table
694 A parent table to associate or de-associate with this table.
695 new_owner
697 The user name of the new owner of the table.
698 new_tablespace
700 The name of the tablespace to which the table will be moved.
701 new_schema
703 The name of the schema to which the table will be moved.
704 partition_name
706 The name of the table to attach as a new partition or to detach
707 from this table.
708 partition_bound_spec
710 The partition bound specification for a new partition. Refer to
711 CREATE TABLE (CREATE_TABLE(7)) for more details on the syntax of
712 the same.
713
715 The key word COLUMN is noise and can be omitted.
716 When a column is added with ADD COLUMN and a non-volatile DEFAULT is
718 specified, the default is evaluated at the time of the statement and
719 the result stored in the table's metadata. That value will be used for
720 the column for all existing rows. If no DEFAULT is specified, NULL is
721 used. In neither case is a rewrite of the table required.
722 Adding a column with a volatile DEFAULT or changing the type of an
724 existing column will require the entire table and its indexes to be
725 rewritten. As an exception, when changing the type of an existing
726 column, if the USING clause does not change the column contents and the
727 old type is either binary coercible to the new type or an unconstrained
728 domain over the new type, a table rewrite is not needed; but any
729 indexes on the affected columns must still be rebuilt. Table and/or
730 index rebuilds may take a significant amount of time for a large table;
731 and will temporarily require as much as double the disk space.
732 Adding a CHECK or NOT NULL constraint requires scanning the table to
734 verify that existing rows meet the constraint, but does not require a
735 table rewrite.
736 Similarly, when attaching a new partition it may be scanned to verify
738 that existing rows meet the partition constraint.
739 The main reason for providing the option to specify multiple changes in
741 a single ALTER TABLE is that multiple table scans or rewrites can
742 thereby be combined into a single pass over the table.
743 Scanning a large table to verify a new foreign key or check constraint
745 can take a long time, and other updates to the table are locked out
746 until the ALTER TABLE ADD CONSTRAINT command is committed. The main
747 purpose of the NOT VALID constraint option is to reduce the impact of
748 adding a constraint on concurrent updates. With NOT VALID, the ADD
749 CONSTRAINT command does not scan the table and can be committed
750 immediately. After that, a VALIDATE CONSTRAINT command can be issued to
751 verify that existing rows satisfy the constraint. The validation step
752 does not need to lock out concurrent updates, since it knows that other
753 transactions will be enforcing the constraint for rows that they insert
754 or update; only pre-existing rows need to be checked. Hence, validation
755 acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered.
756 (If the constraint is a foreign key then a ROW SHARE lock is also
757 required on the table referenced by the constraint.) In addition to
758 improving concurrency, it can be useful to use NOT VALID and VALIDATE
759 CONSTRAINT in cases where the table is known to contain pre-existing
760 violations. Once the constraint is in place, no new violations can be
761 inserted, and the existing problems can be corrected at leisure until
762 VALIDATE CONSTRAINT finally succeeds.
763 The DROP COLUMN form does not physically remove the column, but simply
765 makes it invisible to SQL operations. Subsequent insert and update
766 operations in the table will store a null value for the column. Thus,
767 dropping a column is quick but it will not immediately reduce the
768 on-disk size of your table, as the space occupied by the dropped column
769 is not reclaimed. The space will be reclaimed over time as existing
770 rows are updated.
771 To force immediate reclamation of space occupied by a dropped column,
773 you can execute one of the forms of ALTER TABLE that performs a rewrite
774 of the whole table. This results in reconstructing each row with the
775 dropped column replaced by a null value.
776 The rewriting forms of ALTER TABLE are not MVCC-safe. After a table
778 rewrite, the table will appear empty to concurrent transactions, if
779 they are using a snapshot taken before the rewrite occurred. See
780 Section 13.5 for more details.
781 The USING option of SET DATA TYPE can actually specify any expression
783 involving the old values of the row; that is, it can refer to other
784 columns as well as the one being converted. This allows very general
785 conversions to be done with the SET DATA TYPE syntax. Because of this
786 flexibility, the USING expression is not applied to the column's
787 default value (if any); the result might not be a constant expression
788 as required for a default. This means that when there is no implicit or
789 assignment cast from old to new type, SET DATA TYPE might fail to
790 convert the default even though a USING clause is supplied. In such
791 cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and
792 then use SET DEFAULT to add a suitable new default. Similar
793 considerations apply to indexes and constraints involving the column.
794 If a table has any descendant tables, it is not permitted to add,
796 rename, or change the type of a column in the parent table without
797 doing the same to the descendants. This ensures that the descendants
798 always have columns matching the parent. Similarly, a CHECK constraint
799 cannot be renamed in the parent without also renaming it in all
800 descendants, so that CHECK constraints also match between the parent
801 and its descendants. (That restriction does not apply to index-based
802 constraints, however.) Also, because selecting from the parent also
803 selects from its descendants, a constraint on the parent cannot be
804 marked valid unless it is also marked valid for those descendants. In
805 all of these cases, ALTER TABLE ONLY will be rejected.
806 A recursive DROP COLUMN operation will remove a descendant table's
808 column only if the descendant does not inherit that column from any
809 other parents and never had an independent definition of the column. A
810 nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never
811 removes any descendant columns, but instead marks them as independently
812 defined rather than inherited. A nonrecursive DROP COLUMN command will
813 fail for a partitioned table, because all partitions of a table must
814 have the same columns as the partitioning root.
815 The actions for identity columns (ADD GENERATED, SET etc., DROP
817 IDENTITY), as well as the actions TRIGGER, CLUSTER, OWNER, and
818 TABLESPACE never recurse to descendant tables; that is, they always act
819 as though ONLY were specified. Adding a constraint recurses only for
820 CHECK constraints that are not marked NO INHERIT.
821 Changing any part of a system catalog table is not permitted.
823 Refer to CREATE TABLE (CREATE_TABLE(7)) for a further description of
825 valid parameters. Chapter 5 has further information on inheritance.
826
828 To add a column of type varchar to a table:
829 ALTER TABLE distributors ADD COLUMN address varchar(30);
831 That will cause all existing rows in the table to be filled with null
833 values for the new column.
834 To add a column with a non-null default:
836 ALTER TABLE measurements
838 ADD COLUMN mtime timestamp with time zone DEFAULT now();
839 Existing rows will be filled with the current time as the value of the
841 new column, and then new rows will receive the time of their insertion.
842 To add a column and fill it with a value different from the default to
844 be used later:
845 ALTER TABLE transactions
847 ADD COLUMN status varchar(30) DEFAULT 'old',
848 ALTER COLUMN status SET default 'current';
849 Existing rows will be filled with old, but then the default for
851 subsequent commands will be current. The effects are the same as if the
852 two sub-commands had been issued in separate ALTER TABLE commands.
853 To drop a column from a table:
855 ALTER TABLE distributors DROP COLUMN address RESTRICT;
857 To change the types of two existing columns in one operation:
859 ALTER TABLE distributors
861 ALTER COLUMN address TYPE varchar(80),
862 ALTER COLUMN name TYPE varchar(100);
863 To change an integer column containing Unix timestamps to timestamp
865 with time zone via a USING clause:
866 ALTER TABLE foo
868 ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
869 USING
870 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
871 The same, when the column has a default expression that won't
873 automatically cast to the new data type:
874 ALTER TABLE foo
876 ALTER COLUMN foo_timestamp DROP DEFAULT,
877 ALTER COLUMN foo_timestamp TYPE timestamp with time zone
878 USING
879 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
880 ALTER COLUMN foo_timestamp SET DEFAULT now();
881 To rename an existing column:
883 ALTER TABLE distributors RENAME COLUMN address TO city;
885 To rename an existing table:
887 ALTER TABLE distributors RENAME TO suppliers;
889 To rename an existing constraint:
891 ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
893 To add a not-null constraint to a column:
895 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
897 To remove a not-null constraint from a column:
899 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
901 To add a check constraint to a table and all its children:
903 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
905 To add a check constraint only to a table and not to its children:
907 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
909 (The check constraint will not be inherited by future children,
911 either.)
912 To remove a check constraint from a table and all its children:
914 ALTER TABLE distributors DROP CONSTRAINT zipchk;
916 To remove a check constraint from one table only:
918 ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
920 (The check constraint remains in place for any child tables.)
922 To add a foreign key constraint to a table:
924 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
926 To add a foreign key constraint to a table with the least impact on
928 other work:
929 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
931 ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
932 To add a (multicolumn) unique constraint to a table:
934 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
936 To add an automatically named primary key constraint to a table, noting
938 that a table can only ever have one primary key:
939 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
941 To move a table to a different tablespace:
943 ALTER TABLE distributors SET TABLESPACE fasttablespace;
945 To move a table to a different schema:
947 ALTER TABLE myschema.distributors SET SCHEMA yourschema;
949 To recreate a primary key constraint, without blocking updates while
951 the index is rebuilt:
952 CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
954 ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
955 ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
956 To attach a partition to a range-partitioned table:
958 ALTER TABLE measurement
960 ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
961 To attach a partition to a list-partitioned table:
963 ALTER TABLE cities
965 ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
966 To attach a partition to a hash-partitioned table:
968 ALTER TABLE orders
970 ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
971 To attach a default partition to a partitioned table:
973 ALTER TABLE cities
975 ATTACH PARTITION cities_partdef DEFAULT;
976 To detach a partition from a partitioned table:
978 ALTER TABLE measurement
980 DETACH PARTITION measurement_y2015m12;
981
983 The forms ADD (without USING INDEX), DROP [COLUMN], DROP IDENTITY,
984 RESTART, SET DEFAULT, SET DATA TYPE (without USING), SET GENERATED, and
985 SET sequence_option conform with the SQL standard. The other forms are
986 PostgreSQL extensions of the SQL standard. Also, the ability to specify
987 more than one manipulation in a single ALTER TABLE command is an
988 extension.
989 ALTER TABLE DROP COLUMN can be used to drop the only column of a table,
991 leaving a zero-column table. This is an extension of SQL, which
992 disallows zero-column tables.
993
995 CREATE TABLE (CREATE_TABLE(7))
996PostgreSQL 13.3 2021 ALTER TABLE(7)