1ALTER TABLE(7)           PostgreSQL 13.4 Documentation          ALTER TABLE(7)
2
3
4

NAME

6       ALTER_TABLE - change the definition of a table
7

SYNOPSIS

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
26       where action is one of:
27
28           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
73       and partition_bound_spec is:
74
75       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
80       and column_constraint is:
81
82       [ 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
95       and table_constraint is:
96
97       [ 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
106       and table_constraint_using_index is:
107
108           [ CONSTRAINT constraint_name ]
109           { UNIQUE | PRIMARY KEY } USING INDEX index_name
110           [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
111
112       index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
113
114       [ INCLUDE ( column_name [, ... ] ) ]
115       [ WITH ( storage_parameter [= value] [, ... ] ) ]
116       [ USING INDEX TABLESPACE tablespace_name ]
117
118       exclude_element in an EXCLUDE constraint is:
119
120       { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
121

DESCRIPTION

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
129       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
134       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
145       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
157       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
163       SET/DROP NOT NULL
164           These forms change whether a column is marked to allow null values
165           or to reject null values.
166
167           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
173           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
181       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
186           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
190       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
199           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
203       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
209       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
217           SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock.
218
219       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
244           Changing per-attribute options acquires a SHARE UPDATE EXCLUSIVE
245           lock.
246
247       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
262       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
268           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
280           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
286           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
291       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
296           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
301           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
307           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
311           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
316           This form is not currently supported on partitioned tables.
317
318               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
326       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
330       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
337           This command acquires a SHARE UPDATE EXCLUSIVE lock.
338
339       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
345       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
360           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
368           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
378           This command acquires a SHARE ROW EXCLUSIVE lock.
379
380       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
389           The rule firing mechanism is also affected by the configuration
390           variable session_replication_role, analogous to triggers as
391           described above.
392
393       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
401       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
409       CLUSTER ON
410           This form selects the default index for future CLUSTER(7)
411           operations. It does not actually re-cluster the table.
412
413           Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
414
415       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
420           Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
421
422       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
427       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
437           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
449       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
453       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
465           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
469       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
474       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
483           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
491       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
496       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
503       NOT OF
504           This form dissociates a typed table from its type.
505
506       OWNER TO
507           This form changes the owner of the table, sequence, view,
508           materialized view, or foreign table to the specified user.
509
510       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
524       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
531       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
536       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
552           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
566           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
579           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
584           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
593           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
597           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
603       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
612       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
619       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

PARAMETERS

633       IF EXISTS
634           Do not throw an error if the table does not exist. A notice is
635           issued in this case.
636
637       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
645       column_name
646           Name of a new or existing column.
647
648       new_column_name
649           New name for an existing column.
650
651       new_name
652           New name for the table.
653
654       data_type
655           Data type of the new column, or new data type for an existing
656           column.
657
658       table_constraint
659           New table constraint for the table.
660
661       constraint_name
662           Name of a new or existing constraint.
663
664       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
669       RESTRICT
670           Refuse to drop the column or constraint if there are any dependent
671           objects. This is the default behavior.
672
673       trigger_name
674           Name of a single trigger to disable or enable.
675
676       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
683       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
689       index_name
690           The name of an existing index.
691
692       storage_parameter
693           The name of a table storage parameter.
694
695       value
696           The new value for a table storage parameter. This might be a number
697           or a word depending on the parameter.
698
699       parent_table
700           A parent table to associate or de-associate with this table.
701
702       new_owner
703           The user name of the new owner of the table.
704
705       new_tablespace
706           The name of the tablespace to which the table will be moved.
707
708       new_schema
709           The name of the schema to which the table will be moved.
710
711       partition_name
712           The name of the table to attach as a new partition or to detach
713           from this table.
714
715       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

NOTES

721       The key word COLUMN is noise and can be omitted.
722
723       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
729       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
739       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
743       Similarly, when attaching a new partition it may be scanned to verify
744       that existing rows meet the partition constraint.
745
746       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
750       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
770       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
778       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
783       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
788       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
801       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
813       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
822       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
828       Changing any part of a system catalog table is not permitted.
829
830       Refer to CREATE TABLE (CREATE_TABLE(7)) for a further description of
831       valid parameters.  Chapter 5 has further information on inheritance.
832

EXAMPLES

834       To add a column of type varchar to a table:
835
836           ALTER TABLE distributors ADD COLUMN address varchar(30);
837
838       That will cause all existing rows in the table to be filled with null
839       values for the new column.
840
841       To add a column with a non-null default:
842
843           ALTER TABLE measurements
844             ADD COLUMN mtime timestamp with time zone DEFAULT now();
845
846       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
849       To add a column and fill it with a value different from the default to
850       be used later:
851
852           ALTER TABLE transactions
853             ADD COLUMN status varchar(30) DEFAULT 'old',
854             ALTER COLUMN status SET default 'current';
855
856       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
860       To drop a column from a table:
861
862           ALTER TABLE distributors DROP COLUMN address RESTRICT;
863
864       To change the types of two existing columns in one operation:
865
866           ALTER TABLE distributors
867               ALTER COLUMN address TYPE varchar(80),
868               ALTER COLUMN name TYPE varchar(100);
869
870       To change an integer column containing Unix timestamps to timestamp
871       with time zone via a USING clause:
872
873           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
878       The same, when the column has a default expression that won't
879       automatically cast to the new data type:
880
881           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
888       To rename an existing column:
889
890           ALTER TABLE distributors RENAME COLUMN address TO city;
891
892       To rename an existing table:
893
894           ALTER TABLE distributors RENAME TO suppliers;
895
896       To rename an existing constraint:
897
898           ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
899
900       To add a not-null constraint to a column:
901
902           ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
903
904       To remove a not-null constraint from a column:
905
906           ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
907
908       To add a check constraint to a table and all its children:
909
910           ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
911
912       To add a check constraint only to a table and not to its children:
913
914           ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
915
916       (The check constraint will not be inherited by future children,
917       either.)
918
919       To remove a check constraint from a table and all its children:
920
921           ALTER TABLE distributors DROP CONSTRAINT zipchk;
922
923       To remove a check constraint from one table only:
924
925           ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
926
927       (The check constraint remains in place for any child tables.)
928
929       To add a foreign key constraint to a table:
930
931           ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
932
933       To add a foreign key constraint to a table with the least impact on
934       other work:
935
936           ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
937           ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
938
939       To add a (multicolumn) unique constraint to a table:
940
941           ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
942
943       To add an automatically named primary key constraint to a table, noting
944       that a table can only ever have one primary key:
945
946           ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
947
948       To move a table to a different tablespace:
949
950           ALTER TABLE distributors SET TABLESPACE fasttablespace;
951
952       To move a table to a different schema:
953
954           ALTER TABLE myschema.distributors SET SCHEMA yourschema;
955
956       To recreate a primary key constraint, without blocking updates while
957       the index is rebuilt:
958
959           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
963       To attach a partition to a range-partitioned table:
964
965           ALTER TABLE measurement
966               ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
967
968       To attach a partition to a list-partitioned table:
969
970           ALTER TABLE cities
971               ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
972
973       To attach a partition to a hash-partitioned table:
974
975           ALTER TABLE orders
976               ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
977
978       To attach a default partition to a partitioned table:
979
980           ALTER TABLE cities
981               ATTACH PARTITION cities_partdef DEFAULT;
982
983       To detach a partition from a partitioned table:
984
985           ALTER TABLE measurement
986               DETACH PARTITION measurement_y2015m12;
987

COMPATIBILITY

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
996       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

SEE ALSO

1001       CREATE TABLE (CREATE_TABLE(7))
1002
1003
1004
1005PostgreSQL 13.4                      2021                       ALTER TABLE(7)
Impressum