1ALTER TABLE(7)           PostgreSQL 11.6 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 ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
35           ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
36           ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
37           ALTER [ COLUMN ] column_name SET STATISTICS integer
38           ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
39           ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
40           ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
41           ADD table_constraint [ NOT VALID ]
42           ADD table_constraint_using_index
43           ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
44           VALIDATE CONSTRAINT constraint_name
45           DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]
46           DISABLE TRIGGER [ trigger_name | ALL | USER ]
47           ENABLE TRIGGER [ trigger_name | ALL | USER ]
48           ENABLE REPLICA TRIGGER trigger_name
49           ENABLE ALWAYS TRIGGER trigger_name
50           DISABLE RULE rewrite_rule_name
51           ENABLE RULE rewrite_rule_name
52           ENABLE REPLICA RULE rewrite_rule_name
53           ENABLE ALWAYS RULE rewrite_rule_name
54           DISABLE ROW LEVEL SECURITY
55           ENABLE ROW LEVEL SECURITY
56           FORCE ROW LEVEL SECURITY
57           NO FORCE ROW LEVEL SECURITY
58           CLUSTER ON index_name
59           SET WITHOUT CLUSTER
60           SET WITH OIDS
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 ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] ) |
76       FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
77         TO ( { numeric_literal | string_literal | TRUE | FALSE | 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 | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
88         UNIQUE index_parameters |
89         PRIMARY KEY index_parameters |
90         REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
91           [ ON DELETE action ] [ ON UPDATE action ] }
92       [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
93
94       and table_constraint is:
95
96       [ CONSTRAINT constraint_name ]
97       { CHECK ( expression ) [ NO INHERIT ] |
98         UNIQUE ( column_name [, ... ] ) index_parameters |
99         PRIMARY KEY ( column_name [, ... ] ) index_parameters |
100         EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
101         FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
102           [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
103       [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
104
105       and table_constraint_using_index is:
106
107           [ CONSTRAINT constraint_name ]
108           { UNIQUE | PRIMARY KEY } USING INDEX index_name
109           [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
110
111       index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
112
113       [ INCLUDE ( column_name [, ... ] ) ]
114       [ WITH ( storage_parameter [= value] [, ... ] ) ]
115       [ USING INDEX TABLESPACE tablespace_name ]
116
117       exclude_element in an EXCLUDE constraint is:
118
119       { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
120

DESCRIPTION

122       ALTER TABLE changes the definition of an existing table. There are
123       several subforms described below. Note that the lock level required may
124       differ for each subform. An ACCESS EXCLUSIVE lock is held unless
125       explicitly noted. When multiple subcommands are listed, the lock held
126       will be the strictest one required from any subcommand.
127
128       ADD COLUMN [ IF NOT EXISTS ]
129           This form adds a new column to the table, using the same syntax as
130           CREATE TABLE (CREATE_TABLE(7)). If IF NOT EXISTS is specified and a
131           column already exists with this name, no error is thrown.
132
133       DROP COLUMN [ IF EXISTS ]
134           This form drops a column from a table. Indexes and table
135           constraints involving the column will be automatically dropped as
136           well. Multivariate statistics referencing the dropped column will
137           also be removed if the removal of the column would cause the
138           statistics to contain data for only a single column. You will need
139           to say CASCADE if anything outside the table depends on the column,
140           for example, foreign key references or views. If IF EXISTS is
141           specified and the column does not exist, no error is thrown. In
142           this case a notice is issued instead.
143
144       SET DATA TYPE
145           This form changes the type of a column of a table. Indexes and
146           simple table constraints involving the column will be automatically
147           converted to use the new column type by reparsing the originally
148           supplied expression. The optional COLLATE clause specifies a
149           collation for the new column; if omitted, the collation is the
150           default for the new column type. The optional USING clause
151           specifies how to compute the new column value from the old; if
152           omitted, the default conversion is the same as an assignment cast
153           from old data type to new. A USING clause must be provided if there
154           is no implicit or assignment cast from old to new type.
155
156       SET/DROP DEFAULT
157           These forms set or remove the default value for a column. Default
158           values only apply in subsequent INSERT or UPDATE commands; they do
159           not cause rows already in the table to change.
160
161       SET/DROP NOT NULL
162           These forms change whether a column is marked to allow null values
163           or to reject null values. You can only use SET NOT NULL when the
164           column contains no null values.
165
166           If this table is a partition, one cannot perform DROP NOT NULL on a
167           column if it is marked NOT NULL in the parent table. To drop the
168           NOT NULL constraint from all the partitions, perform DROP NOT NULL
169           on the parent table. Even if there is no NOT NULL constraint on the
170           parent, such a constraint can still be added to individual
171           partitions, if desired; that is, the children can disallow nulls
172           even if the parent allows them, but not the other way around.
173
174       ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
175       SET GENERATED { ALWAYS | BY DEFAULT }
176       DROP IDENTITY [ IF EXISTS ]
177           These forms change whether a column is an identity column or change
178           the generation attribute of an existing identity column. See CREATE
179           TABLE (CREATE_TABLE(7)) for details.
180
181           If DROP IDENTITY IF EXISTS is specified and the column is not an
182           identity column, no error is thrown. In this case a notice is
183           issued instead.
184
185       SET sequence_option
186       RESTART
187           These forms alter the sequence that underlies an existing identity
188           column.  sequence_option is an option supported by ALTER SEQUENCE
189           (ALTER_SEQUENCE(7)) such as INCREMENT BY.
190
191       SET STATISTICS
192           This form sets the per-column statistics-gathering target for
193           subsequent ANALYZE(7) operations. The target can be set in the
194           range 0 to 10000; alternatively, set it to -1 to revert to using
195           the system default statistics target (default_statistics_target).
196           For more information on the use of statistics by the PostgreSQL
197           query planner, refer to Section 14.2.
198
199           SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock.
200
201       SET ( attribute_option = value [, ... ] )
202       RESET ( attribute_option [, ... ] )
203           This form sets or resets per-attribute options. Currently, the only
204           defined per-attribute options are n_distinct and
205           n_distinct_inherited, which override the number-of-distinct-values
206           estimates made by subsequent ANALYZE(7) operations.  n_distinct
207           affects the statistics for the table itself, while
208           n_distinct_inherited affects the statistics gathered for the table
209           plus its inheritance children. When set to a positive value,
210           ANALYZE will assume that the column contains exactly the specified
211           number of distinct nonnull values. When set to a negative value,
212           which must be greater than or equal to -1, ANALYZE will assume that
213           the number of distinct nonnull values in the column is linear in
214           the size of the table; the exact count is to be computed by
215           multiplying the estimated table size by the absolute value of the
216           given number. For example, a value of -1 implies that all values in
217           the column are distinct, while a value of -0.5 implies that each
218           value appears twice on the average. This can be useful when the
219           size of the table changes over time, since the multiplication by
220           the number of rows in the table is not performed until query
221           planning time. Specify a value of 0 to revert to estimating the
222           number of distinct values normally. For more information on the use
223           of statistics by the PostgreSQL query planner, refer to
224           Section 14.2.
225
226           Changing per-attribute options acquires a SHARE UPDATE EXCLUSIVE
227           lock.
228
229       SET STORAGE
230           This form sets the storage mode for a column. This controls whether
231           this column is held inline or in a secondary TOAST table, and
232           whether the data should be compressed or not.  PLAIN must be used
233           for fixed-length values such as integer and is inline,
234           uncompressed.  MAIN is for inline, compressible data.  EXTERNAL is
235           for external, uncompressed data, and EXTENDED is for external,
236           compressed data.  EXTENDED is the default for most data types that
237           support non-PLAIN storage. Use of EXTERNAL will make substring
238           operations on very large text and bytea values run faster, at the
239           penalty of increased storage space. Note that SET STORAGE doesn't
240           itself change anything in the table, it just sets the strategy to
241           be pursued during future table updates. See Section 68.2 for more
242           information.
243
244       ADD table_constraint [ NOT VALID ]
245           This form adds a new constraint to a table using the same syntax as
246           CREATE TABLE (CREATE_TABLE(7)), plus the option NOT VALID, which is
247           currently only allowed for foreign key and CHECK constraints. If
248           the constraint is marked NOT VALID, the potentially-lengthy initial
249           check to verify that all rows in the table satisfy the constraint
250           is skipped. The constraint will still be enforced against
251           subsequent inserts or updates (that is, they'll fail unless there
252           is a matching row in the referenced table, in the case of foreign
253           keys; and they'll fail unless the new row matches the specified
254           check constraints). But the database will not assume that the
255           constraint holds for all rows in the table, until it is validated
256           by using the VALIDATE CONSTRAINT option. Foreign key constraints on
257           partitioned tables may not be declared NOT VALID at present.
258
259           The addition of a foreign key constraint requires a SHARE ROW
260           EXCLUSIVE lock on the referenced table.
261
262           Additional restrictions apply when unique or primary key
263           constraints are added to partitioned tables; see CREATE TABLE
264           (CREATE_TABLE(7)).
265
266       ADD table_constraint_using_index
267           This form adds a new PRIMARY KEY or UNIQUE constraint to a table
268           based on an existing unique index. All the columns of the index
269           will be included in the constraint.
270
271           The index cannot have expression columns nor be a partial index.
272           Also, it must be a b-tree index with default sort ordering. These
273           restrictions ensure that the index is equivalent to one that would
274           be built by a regular ADD PRIMARY KEY or ADD UNIQUE command.
275
276           If PRIMARY KEY is specified, and the index's columns are not
277           already marked NOT NULL, then this command will attempt to do ALTER
278           COLUMN SET NOT NULL against each such column. That requires a full
279           table scan to verify the column(s) contain no nulls. In all other
280           cases, this is a fast operation.
281
282           If a constraint name is provided then the index will be renamed to
283           match the constraint name. Otherwise the constraint will be named
284           the same as the index.
285
286           After this command is executed, the index is “owned” by the
287           constraint, in the same way as if the index had been built by a
288           regular ADD PRIMARY KEY or ADD UNIQUE command. In particular,
289           dropping the constraint will make the index disappear too.
290
291           This form is not currently supported on partitioned tables.
292
293               Note
294               Adding a constraint using an existing index can be helpful in
295               situations where a new constraint needs to be added without
296               blocking table updates for a long time. To do that, create the
297               index using CREATE INDEX CONCURRENTLY, and then install it as
298               an official constraint using this syntax. See the example
299               below.
300
301       ALTER CONSTRAINT
302           This form alters the attributes of a constraint that was previously
303           created. Currently only foreign key constraints may be altered.
304
305       VALIDATE CONSTRAINT
306           This form validates a foreign key or check constraint that was
307           previously created as NOT VALID, by scanning the table to ensure
308           there are no rows for which the constraint is not satisfied.
309           Nothing happens if the constraint is already marked valid.
310
311           Validation can be a long process on larger tables. The value of
312           separating validation from initial creation is that you can defer
313           validation to less busy times, or can be used to give additional
314           time to correct pre-existing errors while preventing new errors.
315           Note also that validation on its own does not prevent normal write
316           commands against the table while it runs.
317
318           Validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table
319           being altered. If the constraint is a foreign key then a ROW SHARE
320           lock is also required on the table referenced by the constraint.
321
322       DROP CONSTRAINT [ IF EXISTS ]
323           This form drops the specified constraint on a table, along with any
324           index underlying the constraint. If IF EXISTS is specified and the
325           constraint does not exist, no error is thrown. In this case a
326           notice is issued instead.
327
328       DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
329           These forms configure the firing of trigger(s) belonging to the
330           table. A disabled trigger is still known to the system, but is not
331           executed when its triggering event occurs. For a deferred trigger,
332           the enable status is checked when the event occurs, not when the
333           trigger function is actually executed. One can disable or enable a
334           single trigger specified by name, or all triggers on the table, or
335           only user triggers (this option excludes internally generated
336           constraint triggers such as those that are used to implement
337           foreign key constraints or deferrable uniqueness and exclusion
338           constraints). Disabling or enabling internally generated constraint
339           triggers requires superuser privileges; it should be done with
340           caution since of course the integrity of the constraint cannot be
341           guaranteed if the triggers are not executed.
342
343           The trigger firing mechanism is also affected by the configuration
344           variable session_replication_role. Simply enabled triggers (the
345           default) will fire when the replication role is “origin” (the
346           default) or “local”. Triggers configured as ENABLE REPLICA will
347           only fire if the session is in “replica” mode, and triggers
348           configured as ENABLE ALWAYS will fire regardless of the current
349           replication role.
350
351           The effect of this mechanism is that in the default configuration,
352           triggers do not fire on replicas. This is useful because if a
353           trigger is used on the origin to propagate data between tables,
354           then the replication system will also replicate the propagated
355           data, and the trigger should not fire a second time on the replica,
356           because that would lead to duplication. However, if a trigger is
357           used for another purpose such as creating external alerts, then it
358           might be appropriate to set it to ENABLE ALWAYS so that it is also
359           fired on replicas.
360
361           This command acquires a SHARE ROW EXCLUSIVE lock.
362
363       DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
364           These forms configure the firing of rewrite rules belonging to the
365           table. A disabled rule is still known to the system, but is not
366           applied during query rewriting. The semantics are as for
367           disabled/enabled triggers. This configuration is ignored for ON
368           SELECT rules, which are always applied in order to keep views
369           working even if the current session is in a non-default replication
370           role.
371
372           The rule firing mechanism is also affected by the configuration
373           variable session_replication_role, analogous to triggers as
374           described above.
375
376       DISABLE/ENABLE ROW LEVEL SECURITY
377           These forms control the application of row security policies
378           belonging to the table. If enabled and no policies exist for the
379           table, then a default-deny policy is applied. Note that policies
380           can exist for a table even if row level security is disabled - in
381           this case, the policies will NOT be applied and the policies will
382           be ignored. See also CREATE POLICY (CREATE_POLICY(7)).
383
384       NO FORCE/FORCE ROW LEVEL SECURITY
385           These forms control the application of row security policies
386           belonging to the table when the user is the table owner. If
387           enabled, row level security policies will be applied when the user
388           is the table owner. If disabled (the default) then row level
389           security will not be applied when the user is the table owner. See
390           also CREATE POLICY (CREATE_POLICY(7)).
391
392       CLUSTER ON
393           This form selects the default index for future CLUSTER(7)
394           operations. It does not actually re-cluster the table.
395
396           Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
397
398       SET WITHOUT CLUSTER
399           This form removes the most recently used CLUSTER(7) index
400           specification from the table. This affects future cluster
401           operations that don't specify an index.
402
403           Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
404
405       SET WITH OIDS
406           This form adds an oid system column to the table (see Section 5.4).
407           It does nothing if the table already has OIDs.
408
409           Note that this is not equivalent to ADD COLUMN oid oid; that would
410           add a normal column that happened to be named oid, not a system
411           column.
412
413       SET WITHOUT OIDS
414           This form removes the oid system column from the table. This is
415           exactly equivalent to DROP COLUMN oid RESTRICT, except that it will
416           not complain if there is already no oid column.
417
418       SET TABLESPACE
419           This form changes the table's tablespace to the specified
420           tablespace and moves the data file(s) associated with the table to
421           the new tablespace. Indexes on the table, if any, are not moved;
422           but they can be moved separately with additional SET TABLESPACE
423           commands. All tables in the current database in a tablespace can be
424           moved by using the ALL IN TABLESPACE form, which will lock all
425           tables to be moved first and then move each one. This form also
426           supports OWNED BY, which will only move tables owned by the roles
427           specified. If the NOWAIT option is specified then the command will
428           fail if it is unable to acquire all of the locks required
429           immediately. Note that system catalogs are not moved by this
430           command, use ALTER DATABASE or explicit ALTER TABLE invocations
431           instead if desired. The information_schema relations are not
432           considered part of the system catalogs and will be moved. See also
433           CREATE TABLESPACE (CREATE_TABLESPACE(7)).
434
435       SET { LOGGED | UNLOGGED }
436           This form changes the table from unlogged to logged or vice-versa
437           (see UNLOGGED). It cannot be applied to a temporary table.
438
439       SET ( storage_parameter = value [, ... ] )
440           This form changes one or more storage parameters for the table. See
441           Storage Parameters for details on the available parameters. Note
442           that the table contents will not be modified immediately by this
443           command; depending on the parameter you might need to rewrite the
444           table to get the desired effects. That can be done with VACUUM
445           FULL, CLUSTER(7) or one of the forms of ALTER TABLE that forces a
446           table rewrite. For planner related parameters, changes will take
447           effect from the next time the table is locked so currently
448           executing queries will not be affected.
449
450           SHARE UPDATE EXCLUSIVE lock will be taken for fillfactor, toast and
451           autovacuum storage parameters, as well as the following planner
452           related parameters: effective_io_concurrency, parallel_workers,
453           seq_page_cost, random_page_cost, n_distinct and
454           n_distinct_inherited.
455
456               Note
457               While CREATE TABLE allows OIDS to be specified in the WITH
458               (storage_parameter) syntax, ALTER TABLE does not treat OIDS as
459               a storage parameter. Instead use the SET WITH OIDS and SET
460               WITHOUT OIDS forms to change OID status.
461
462       RESET ( storage_parameter [, ... ] )
463           This form resets one or more storage parameters to their defaults.
464           As with SET, a table rewrite might be needed to update the table
465           entirely.
466
467       INHERIT parent_table
468           This form adds the target table as a new child of the specified
469           parent table. Subsequently, queries against the parent will include
470           records of the target table. To be added as a child, the target
471           table must already contain all the same columns as the parent (it
472           could have additional columns, too). The columns must have matching
473           data types, and if they have NOT NULL constraints in the parent
474           then they must also have NOT NULL constraints in the child.
475
476           There must also be matching child-table constraints for all CHECK
477           constraints of the parent, except those marked non-inheritable
478           (that is, created with ALTER TABLE ... ADD CONSTRAINT ... NO
479           INHERIT) in the parent, which are ignored; all child-table
480           constraints matched must not be marked non-inheritable. Currently
481           UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are not
482           considered, but this might change in the future.
483
484       NO INHERIT parent_table
485           This form removes the target table from the list of children of the
486           specified parent table. Queries against the parent table will no
487           longer include records drawn from the target table.
488
489       OF type_name
490           This form links the table to a composite type as though CREATE
491           TABLE OF had formed it. The table's list of column names and types
492           must precisely match that of the composite type; the presence of an
493           oid system column is permitted to differ. The table must not
494           inherit from any other table. These restrictions ensure that CREATE
495           TABLE OF would permit an equivalent table definition.
496
497       NOT OF
498           This form dissociates a typed table from its type.
499
500       OWNER TO
501           This form changes the owner of the table, sequence, view,
502           materialized view, or foreign table to the specified user.
503
504       REPLICA IDENTITY
505           This form changes the information which is written to the
506           write-ahead log to identify rows which are updated or deleted. This
507           option has no effect except when logical replication is in use.
508           DEFAULT (the default for non-system tables) records the old values
509           of the columns of the primary key, if any.  USING INDEX records the
510           old values of the columns covered by the named index, which must be
511           unique, not partial, not deferrable, and include only columns
512           marked NOT NULL.  FULL records the old values of all columns in the
513           row.  NOTHING records no information about the old row. (This is
514           the default for system tables.) In all cases, no old values are
515           logged unless at least one of the columns that would be logged
516           differs between the old and new versions of the row.
517
518       RENAME
519           The RENAME forms change the name of a table (or an index, sequence,
520           view, materialized view, or foreign table), the name of an
521           individual column in a table, or the name of a constraint of the
522           table. When renaming a constraint that has an underlying index, the
523           index is renamed as well. There is no effect on the stored data.
524
525       SET SCHEMA
526           This form moves the table into another schema. Associated indexes,
527           constraints, and sequences owned by table columns are moved as
528           well.
529
530       ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec |
531       DEFAULT }
532           This form attaches an existing table (which might itself be
533           partitioned) as a partition of the target table. The table can be
534           attached as a partition for specific values using FOR VALUES or as
535           a default partition by using DEFAULT. For each index in the target
536           table, a corresponding one will be created in the attached table;
537           or, if an equivalent index already exists, it will be attached to
538           the target table's index, as if ALTER INDEX ATTACH PARTITION had
539           been executed. Note that if the existing table is a foreign table,
540           it is currently not allowed to attach the table as a partition of
541           the target table if there are UNIQUE indexes on the target table.
542           (See also CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)).)
543
544           A partition using FOR VALUES uses same syntax for
545           partition_bound_spec as CREATE TABLE (CREATE_TABLE(7)). The
546           partition bound specification must correspond to the partitioning
547           strategy and partition key of the target table. The table to be
548           attached must have all the same columns as the target table and no
549           more; moreover, the column types must also match. Also, it must
550           have all the NOT NULL and CHECK constraints of the target table.
551           Currently FOREIGN KEY constraints are not considered.  UNIQUE and
552           PRIMARY KEY constraints from the parent table will be created in
553           the partition, if they don't already exist. If any of the CHECK
554           constraints of the table being attached is marked NO INHERIT, the
555           command will fail; such constraints must be recreated without the
556           NO INHERIT clause.
557
558           If the new partition is a regular table, a full table scan is
559           performed to check that existing rows in the table do not violate
560           the partition constraint. It is possible to avoid this scan by
561           adding a valid CHECK constraint to the table that allows only rows
562           satisfying the desired partition constraint before running this
563           command. The CHECK constraint will be used to determine that the
564           table need not be scanned to validate the partition constraint.
565           This does not work, however, if any of the partition keys is an
566           expression and the partition does not accept NULL values. If
567           attaching a list partition that will not accept NULL values, also
568           add NOT NULL constraint to the partition key column, unless it's an
569           expression.
570
571           If the new partition is a foreign table, nothing is done to verify
572           that all the rows in the foreign table obey the partition
573           constraint. (See the discussion in CREATE FOREIGN TABLE
574           (CREATE_FOREIGN_TABLE(7)) about constraints on the foreign table.)
575
576           When a table has a default partition, defining a new partition
577           changes the partition constraint for the default partition. The
578           default partition can't contain any rows that would need to be
579           moved to the new partition, and will be scanned to verify that none
580           are present. This scan, like the scan of the new partition, can be
581           avoided if an appropriate CHECK constraint is present. Also like
582           the scan of the new partition, it is always skipped when the
583           default partition is a foreign table.
584
585       DETACH PARTITION partition_name
586           This form detaches specified partition of the target table. The
587           detached partition continues to exist as a standalone table, but no
588           longer has any ties to the table from which it was detached. Any
589           indexes that were attached to the target table's indexes are
590           detached.
591
592       All the forms of ALTER TABLE that act on a single table, except RENAME,
593       SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION can be combined into
594       a list of multiple alterations to be applied together. For example, it
595       is possible to add several columns and/or alter the type of several
596       columns in a single command. This is particularly useful with large
597       tables, since only one pass over the table need be made.
598
599       You must own the table to use ALTER TABLE. To change the schema or
600       tablespace of a table, you must also have CREATE privilege on the new
601       schema or tablespace. To add the table as a new child of a parent
602       table, you must own the parent table as well. Also, to attach a table
603       as a new partition of the table, you must own the table being attached.
604       To alter the owner, you must also be a direct or indirect member of the
605       new owning role, and that role must have CREATE privilege on the
606       table's schema. (These restrictions enforce that altering the owner
607       doesn't do anything you couldn't do by dropping and recreating the
608       table. However, a superuser can alter ownership of any table anyway.)
609       To add a column or alter a column type or use the OF clause, you must
610       also have USAGE privilege on the data type.
611

PARAMETERS

613       IF EXISTS
614           Do not throw an error if the table does not exist. A notice is
615           issued in this case.
616
617       name
618           The name (optionally schema-qualified) of an existing table to
619           alter. If ONLY is specified before the table name, only that table
620           is altered. If ONLY is not specified, the table and all its
621           descendant tables (if any) are altered. Optionally, * can be
622           specified after the table name to explicitly indicate that
623           descendant tables are included.
624
625       column_name
626           Name of a new or existing column.
627
628       new_column_name
629           New name for an existing column.
630
631       new_name
632           New name for the table.
633
634       data_type
635           Data type of the new column, or new data type for an existing
636           column.
637
638       table_constraint
639           New table constraint for the table.
640
641       constraint_name
642           Name of a new or existing constraint.
643
644       CASCADE
645           Automatically drop objects that depend on the dropped column or
646           constraint (for example, views referencing the column), and in turn
647           all objects that depend on those objects (see Section 5.13).
648
649       RESTRICT
650           Refuse to drop the column or constraint if there are any dependent
651           objects. This is the default behavior.
652
653       trigger_name
654           Name of a single trigger to disable or enable.
655
656       ALL
657           Disable or enable all triggers belonging to the table. (This
658           requires superuser privilege if any of the triggers are internally
659           generated constraint triggers such as those that are used to
660           implement foreign key constraints or deferrable uniqueness and
661           exclusion constraints.)
662
663       USER
664           Disable or enable all triggers belonging to the table except for
665           internally generated constraint triggers such as those that are
666           used to implement foreign key constraints or deferrable uniqueness
667           and exclusion constraints.
668
669       index_name
670           The name of an existing index.
671
672       storage_parameter
673           The name of a table storage parameter.
674
675       value
676           The new value for a table storage parameter. This might be a number
677           or a word depending on the parameter.
678
679       parent_table
680           A parent table to associate or de-associate with this table.
681
682       new_owner
683           The user name of the new owner of the table.
684
685       new_tablespace
686           The name of the tablespace to which the table will be moved.
687
688       new_schema
689           The name of the schema to which the table will be moved.
690
691       partition_name
692           The name of the table to attach as a new partition or to detach
693           from this table.
694
695       partition_bound_spec
696           The partition bound specification for a new partition. Refer to
697           CREATE TABLE (CREATE_TABLE(7)) for more details on the syntax of
698           the same.
699

NOTES

701       The key word COLUMN is noise and can be omitted.
702
703       When a column is added with ADD COLUMN and a non-volatile DEFAULT is
704       specified, the default is evaluated at the time of the statement and
705       the result stored in the table's metadata. That value will be used for
706       the column for all existing rows. If no DEFAULT is specified, NULL is
707       used. In neither case is a rewrite of the table required.
708
709       Adding a column with a volatile DEFAULT or changing the type of an
710       existing column will require the entire table and its indexes to be
711       rewritten. As an exception, when changing the type of an existing
712       column, if the USING clause does not change the column contents and the
713       old type is either binary coercible to the new type or an unconstrained
714       domain over the new type, a table rewrite is not needed; but any
715       indexes on the affected columns must still be rebuilt. Adding or
716       removing a system oid column also requires rewriting the entire table.
717       Table and/or index rebuilds may take a significant amount of time for a
718       large table; and will temporarily require as much as double the disk
719       space.
720
721       Adding a CHECK or NOT NULL constraint requires scanning the table to
722       verify that existing rows meet the constraint, but does not require a
723       table rewrite.
724
725       Similarly, when attaching a new partition it may be scanned to verify
726       that existing rows meet the partition constraint.
727
728       The main reason for providing the option to specify multiple changes in
729       a single ALTER TABLE is that multiple table scans or rewrites can
730       thereby be combined into a single pass over the table.
731
732       The DROP COLUMN form does not physically remove the column, but simply
733       makes it invisible to SQL operations. Subsequent insert and update
734       operations in the table will store a null value for the column. Thus,
735       dropping a column is quick but it will not immediately reduce the
736       on-disk size of your table, as the space occupied by the dropped column
737       is not reclaimed. The space will be reclaimed over time as existing
738       rows are updated. (These statements do not apply when dropping the
739       system oid column; that is done with an immediate rewrite.)
740
741       To force immediate reclamation of space occupied by a dropped column,
742       you can execute one of the forms of ALTER TABLE that performs a rewrite
743       of the whole table. This results in reconstructing each row with the
744       dropped column replaced by a null value.
745
746       The rewriting forms of ALTER TABLE are not MVCC-safe. After a table
747       rewrite, the table will appear empty to concurrent transactions, if
748       they are using a snapshot taken before the rewrite occurred. See
749       Section 13.5 for more details.
750
751       The USING option of SET DATA TYPE can actually specify any expression
752       involving the old values of the row; that is, it can refer to other
753       columns as well as the one being converted. This allows very general
754       conversions to be done with the SET DATA TYPE syntax. Because of this
755       flexibility, the USING expression is not applied to the column's
756       default value (if any); the result might not be a constant expression
757       as required for a default. This means that when there is no implicit or
758       assignment cast from old to new type, SET DATA TYPE might fail to
759       convert the default even though a USING clause is supplied. In such
760       cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and
761       then use SET DEFAULT to add a suitable new default. Similar
762       considerations apply to indexes and constraints involving the column.
763
764       If a table has any descendant tables, it is not permitted to add,
765       rename, or change the type of a column in the parent table without
766       doing the same to the descendants. This ensures that the descendants
767       always have columns matching the parent. Similarly, a CHECK constraint
768       cannot be renamed in the parent without also renaming it in all
769       descendants, so that CHECK constraints also match between the parent
770       and its descendants. (That restriction does not apply to index-based
771       constraints, however.) Also, because selecting from the parent also
772       selects from its descendants, a constraint on the parent cannot be
773       marked valid unless it is also marked valid for those descendants. In
774       all of these cases, ALTER TABLE ONLY will be rejected.
775
776       A recursive DROP COLUMN operation will remove a descendant table's
777       column only if the descendant does not inherit that column from any
778       other parents and never had an independent definition of the column. A
779       nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never
780       removes any descendant columns, but instead marks them as independently
781       defined rather than inherited. A nonrecursive DROP COLUMN command will
782       fail for a partitioned table, because all partitions of a table must
783       have the same columns as the partitioning root.
784
785       The actions for identity columns (ADD GENERATED, SET etc., DROP
786       IDENTITY), as well as the actions TRIGGER, CLUSTER, OWNER, and
787       TABLESPACE never recurse to descendant tables; that is, they always act
788       as though ONLY were specified. Adding a constraint recurses only for
789       CHECK constraints that are not marked NO INHERIT.
790
791       Changing any part of a system catalog table is not permitted.
792
793       Refer to CREATE TABLE (CREATE_TABLE(7)) for a further description of
794       valid parameters.  Chapter 5 has further information on inheritance.
795

EXAMPLES

797       To add a column of type varchar to a table:
798
799           ALTER TABLE distributors ADD COLUMN address varchar(30);
800
801       To drop a column from a table:
802
803           ALTER TABLE distributors DROP COLUMN address RESTRICT;
804
805       To change the types of two existing columns in one operation:
806
807           ALTER TABLE distributors
808               ALTER COLUMN address TYPE varchar(80),
809               ALTER COLUMN name TYPE varchar(100);
810
811       To change an integer column containing Unix timestamps to timestamp
812       with time zone via a USING clause:
813
814           ALTER TABLE foo
815               ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
816               USING
817                   timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
818
819       The same, when the column has a default expression that won't
820       automatically cast to the new data type:
821
822           ALTER TABLE foo
823               ALTER COLUMN foo_timestamp DROP DEFAULT,
824               ALTER COLUMN foo_timestamp TYPE timestamp with time zone
825               USING
826                   timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
827               ALTER COLUMN foo_timestamp SET DEFAULT now();
828
829       To rename an existing column:
830
831           ALTER TABLE distributors RENAME COLUMN address TO city;
832
833       To rename an existing table:
834
835           ALTER TABLE distributors RENAME TO suppliers;
836
837       To rename an existing constraint:
838
839           ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
840
841       To add a not-null constraint to a column:
842
843           ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
844
845       To remove a not-null constraint from a column:
846
847           ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
848
849       To add a check constraint to a table and all its children:
850
851           ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
852
853       To add a check constraint only to a table and not to its children:
854
855           ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
856
857       (The check constraint will not be inherited by future children,
858       either.)
859
860       To remove a check constraint from a table and all its children:
861
862           ALTER TABLE distributors DROP CONSTRAINT zipchk;
863
864       To remove a check constraint from one table only:
865
866           ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
867
868       (The check constraint remains in place for any child tables.)
869
870       To add a foreign key constraint to a table:
871
872           ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
873
874       To add a foreign key constraint to a table with the least impact on
875       other work:
876
877           ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
878           ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
879
880       To add a (multicolumn) unique constraint to a table:
881
882           ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
883
884       To add an automatically named primary key constraint to a table, noting
885       that a table can only ever have one primary key:
886
887           ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
888
889       To move a table to a different tablespace:
890
891           ALTER TABLE distributors SET TABLESPACE fasttablespace;
892
893       To move a table to a different schema:
894
895           ALTER TABLE myschema.distributors SET SCHEMA yourschema;
896
897       To recreate a primary key constraint, without blocking updates while
898       the index is rebuilt:
899
900           CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
901           ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
902               ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
903
904       To attach a partition to a range-partitioned table:
905
906           ALTER TABLE measurement
907               ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
908
909       To attach a partition to a list-partitioned table:
910
911           ALTER TABLE cities
912               ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
913
914       To attach a partition to a hash-partitioned table:
915
916           ALTER TABLE orders
917               ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
918
919       To attach a default partition to a partitioned table:
920
921           ALTER TABLE cities
922               ATTACH PARTITION cities_partdef DEFAULT;
923
924       To detach a partition from a partitioned table:
925
926           ALTER TABLE measurement
927               DETACH PARTITION measurement_y2015m12;
928

COMPATIBILITY

930       The forms ADD (without USING INDEX), DROP [COLUMN], DROP IDENTITY,
931       RESTART, SET DEFAULT, SET DATA TYPE (without USING), SET GENERATED, and
932       SET sequence_option conform with the SQL standard. The other forms are
933       PostgreSQL extensions of the SQL standard. Also, the ability to specify
934       more than one manipulation in a single ALTER TABLE command is an
935       extension.
936
937       ALTER TABLE DROP COLUMN can be used to drop the only column of a table,
938       leaving a zero-column table. This is an extension of SQL, which
939       disallows zero-column tables.
940

SEE ALSO

942       CREATE TABLE (CREATE_TABLE(7))
943
944
945
946PostgreSQL 11.6                      2019                       ALTER TABLE(7)
Impressum