1ALTER TABLE(7)           PostgreSQL 13.3 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 68.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 ACCESS EXCLUSIVE locks on the table to
595           be attached and on the default partition (if any).
596
597       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
606       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
613       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

PARAMETERS

627       IF EXISTS
628           Do not throw an error if the table does not exist. A notice is
629           issued in this case.
630
631       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
639       column_name
640           Name of a new or existing column.
641
642       new_column_name
643           New name for an existing column.
644
645       new_name
646           New name for the table.
647
648       data_type
649           Data type of the new column, or new data type for an existing
650           column.
651
652       table_constraint
653           New table constraint for the table.
654
655       constraint_name
656           Name of a new or existing constraint.
657
658       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
663       RESTRICT
664           Refuse to drop the column or constraint if there are any dependent
665           objects. This is the default behavior.
666
667       trigger_name
668           Name of a single trigger to disable or enable.
669
670       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
677       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
683       index_name
684           The name of an existing index.
685
686       storage_parameter
687           The name of a table storage parameter.
688
689       value
690           The new value for a table storage parameter. This might be a number
691           or a word depending on the parameter.
692
693       parent_table
694           A parent table to associate or de-associate with this table.
695
696       new_owner
697           The user name of the new owner of the table.
698
699       new_tablespace
700           The name of the tablespace to which the table will be moved.
701
702       new_schema
703           The name of the schema to which the table will be moved.
704
705       partition_name
706           The name of the table to attach as a new partition or to detach
707           from this table.
708
709       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

NOTES

715       The key word COLUMN is noise and can be omitted.
716
717       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
723       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
733       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
737       Similarly, when attaching a new partition it may be scanned to verify
738       that existing rows meet the partition constraint.
739
740       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
744       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
764       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
772       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
777       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
782       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
795       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
807       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
816       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
822       Changing any part of a system catalog table is not permitted.
823
824       Refer to CREATE TABLE (CREATE_TABLE(7)) for a further description of
825       valid parameters.  Chapter 5 has further information on inheritance.
826

EXAMPLES

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

COMPATIBILITY

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

SEE ALSO

995       CREATE TABLE (CREATE_TABLE(7))
996
997
998
999PostgreSQL 13.3                      2021                       ALTER TABLE(7)
Impressum