1ALTER TABLE(7) PostgreSQL 11.3 Documentation ALTER TABLE(7)
2
6 ALTER_TABLE - change the definition of a table
7
9 ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
10 action [, ... ]
11 ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
12 RENAME [ COLUMN ] column_name TO new_column_name
13 ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]
14 RENAME CONSTRAINT constraint_name TO new_constraint_name
15 ALTER TABLE [ IF EXISTS ] name
16 RENAME TO new_name
17 ALTER TABLE [ IF EXISTS ] name
18 SET SCHEMA new_schema
19 ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
20 SET TABLESPACE new_tablespace [ NOWAIT ]
21 ALTER TABLE [ IF EXISTS ] name
22 ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }
23 ALTER TABLE [ IF EXISTS ] name
24 DETACH PARTITION partition_name
25 where action is one of:
27 ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
29 DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
30 ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
31 ALTER [ COLUMN ] column_name SET DEFAULT expression
32 ALTER [ COLUMN ] column_name DROP DEFAULT
33 ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
34 ALTER [ COLUMN ] column_name 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 and partition_bound_spec is:
74 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 and column_constraint is:
81 [ CONSTRAINT constraint_name ]
83 { NOT NULL |
84 NULL |
85 CHECK ( expression ) [ NO INHERIT ] |
86 DEFAULT default_expr |
87 GENERATED { ALWAYS | 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 and table_constraint is:
95 [ 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 and table_constraint_using_index is:
106 [ CONSTRAINT constraint_name ]
108 { UNIQUE | PRIMARY KEY } USING INDEX index_name
109 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
110 index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
112 [ INCLUDE ( column_name [, ... ] ) ]
114 [ WITH ( storage_parameter [= value] [, ... ] ) ]
115 [ USING INDEX TABLESPACE tablespace_name ]
116 exclude_element in an EXCLUDE constraint is:
118 { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
120
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 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 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 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 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 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 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 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 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 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 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 SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock.
200 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 Changing per-attribute options acquires a SHARE UPDATE EXCLUSIVE
227 lock.
228 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 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 The addition of a foreign key constraint requires a SHARE ROW
260 EXCLUSIVE lock on the referenced table.
261 Additional restrictions apply when unique or primary key
263 constraints are added to partitioned tables; see CREATE TABLE
264 (CREATE_TABLE(7)).
265 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 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 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 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 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 This form is not currently supported on partitioned tables.
292 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 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 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 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 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 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 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 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 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 This command acquires a SHARE ROW EXCLUSIVE lock.
362 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 The rule firing mechanism is also affected by the configuration
373 variable session_replication_role, analogous to triggers as
374 described above.
375 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 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 CLUSTER ON
393 This form selects the default index for future CLUSTER(7)
394 operations. It does not actually re-cluster the table.
395 Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
397 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 Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
404 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 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 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 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 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 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 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 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 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 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 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 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 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 NOT OF
498 This form dissociates a typed table from its type.
499 OWNER TO
501 This form changes the owner of the table, sequence, view,
502 materialized view, or foreign table to the specified user.
503 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 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 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 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, will be attached to the
538 target table's index, as if ALTER INDEX ATTACH PARTITION had been
539 executed. Note that if the existing table is a foreign table, it is
540 currently not allowed to attach the table as a partition of the
541 target table if there are indexes on the target table. (See also
542 CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)).)
543 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 a constraint must be recreated without the
556 NO INHERIT clause.
557 If the new partition is a regular table, a full table scan is
559 performed to check that no existing row in the table violates the
560 partition constraint. It is possible to avoid this scan by adding a
561 valid CHECK constraint to the table that would allow only the rows
562 satisfying the desired partition constraint before running this
563 command. It will be determined using such a constraint 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 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 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 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 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 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
613 IF EXISTS
614 Do not throw an error if the table does not exist. A notice is
615 issued in this case.
616 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 column_name
626 Name of a new or existing column.
627 new_column_name
629 New name for an existing column.
630 new_name
632 New name for the table.
633 data_type
635 Data type of the new column, or new data type for an existing
636 column.
637 table_constraint
639 New table constraint for the table.
640 constraint_name
642 Name of a new or existing constraint.
643 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 RESTRICT
650 Refuse to drop the column or constraint if there are any dependent
651 objects. This is the default behavior.
652 trigger_name
654 Name of a single trigger to disable or enable.
655 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 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 index_name
670 The name of an existing index.
671 storage_parameter
673 The name of a table storage parameter.
674 value
676 The new value for a table storage parameter. This might be a number
677 or a word depending on the parameter.
678 parent_table
680 A parent table to associate or de-associate with this table.
681 new_owner
683 The user name of the new owner of the table.
684 new_tablespace
686 The name of the tablespace to which the table will be moved.
687 new_schema
689 The name of the schema to which the table will be moved.
690 partition_name
692 The name of the table to attach as a new partition or to detach
693 from this table.
694 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
701 The key word COLUMN is noise and can be omitted.
702 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 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 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 Similarly, when attaching a new partition it may be scanned to verify
726 that existing rows meet the partition constraint.
727 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 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 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 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 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 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 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 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 Changing any part of a system catalog table is not permitted.
792 Refer to CREATE TABLE (CREATE_TABLE(7)) for a further description of
794 valid parameters. Chapter 5 has further information on inheritance.
795
797 To add a column of type varchar to a table:
798 ALTER TABLE distributors ADD COLUMN address varchar(30);
800 To drop a column from a table:
802 ALTER TABLE distributors DROP COLUMN address RESTRICT;
804 To change the types of two existing columns in one operation:
806 ALTER TABLE distributors
808 ALTER COLUMN address TYPE varchar(80),
809 ALTER COLUMN name TYPE varchar(100);
810 To change an integer column containing Unix timestamps to timestamp
812 with time zone via a USING clause:
813 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 The same, when the column has a default expression that won't
820 automatically cast to the new data type:
821 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 To rename an existing column:
830 ALTER TABLE distributors RENAME COLUMN address TO city;
832 To rename an existing table:
834 ALTER TABLE distributors RENAME TO suppliers;
836 To rename an existing constraint:
838 ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
840 To add a not-null constraint to a column:
842 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
844 To remove a not-null constraint from a column:
846 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
848 To add a check constraint to a table and all its children:
850 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
852 To add a check constraint only to a table and not to its children:
854 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
856 (The check constraint will not be inherited by future children,
858 either.)
859 To remove a check constraint from a table and all its children:
861 ALTER TABLE distributors DROP CONSTRAINT zipchk;
863 To remove a check constraint from one table only:
865 ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
867 (The check constraint remains in place for any child tables.)
869 To add a foreign key constraint to a table:
871 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
873 To add a foreign key constraint to a table with the least impact on
875 other work:
876 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
878 ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
879 To add a (multicolumn) unique constraint to a table:
881 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
883 To add an automatically named primary key constraint to a table, noting
885 that a table can only ever have one primary key:
886 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
888 To move a table to a different tablespace:
890 ALTER TABLE distributors SET TABLESPACE fasttablespace;
892 To move a table to a different schema:
894 ALTER TABLE myschema.distributors SET SCHEMA yourschema;
896 To recreate a primary key constraint, without blocking updates while
898 the index is rebuilt:
899 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 To attach a partition to a range-partitioned table:
905 ALTER TABLE measurement
907 ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
908 To attach a partition to a list-partitioned table:
910 ALTER TABLE cities
912 ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
913 To attach a partition to a hash-partitioned table:
915 ALTER TABLE orders
917 ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
918 To attach a default partition to a partitioned table:
920 ALTER TABLE cities
922 ATTACH PARTITION cities_partdef DEFAULT;
923 To detach a partition from a partitioned table:
925 ALTER TABLE measurement
927 DETACH PARTITION measurement_y2015m12;
928
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 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
942 CREATE TABLE (CREATE_TABLE(7))
943PostgreSQL 11.3 2019 ALTER TABLE(7)