1ALTER TABLE(7) PostgreSQL 14.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 [ CONCURRENTLY | FINALIZE ]
25 where action is one of:
27 ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
29 DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]
30 ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
31 ALTER [ COLUMN ] column_name SET DEFAULT expression
32 ALTER [ COLUMN ] column_name DROP DEFAULT
33 ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL
34 ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]
35 ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
36 ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
37 ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]
38 ALTER [ COLUMN ] column_name SET STATISTICS integer
39 ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
40 ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
41 ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
42 ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
43 ADD table_constraint [ NOT VALID ]
44 ADD table_constraint_using_index
45 ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
46 VALIDATE CONSTRAINT constraint_name
47 DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ]
48 DISABLE TRIGGER [ trigger_name | ALL | USER ]
49 ENABLE TRIGGER [ trigger_name | ALL | USER ]
50 ENABLE REPLICA TRIGGER trigger_name
51 ENABLE ALWAYS TRIGGER trigger_name
52 DISABLE RULE rewrite_rule_name
53 ENABLE RULE rewrite_rule_name
54 ENABLE REPLICA RULE rewrite_rule_name
55 ENABLE ALWAYS RULE rewrite_rule_name
56 DISABLE ROW LEVEL SECURITY
57 ENABLE ROW LEVEL SECURITY
58 FORCE ROW LEVEL SECURITY
59 NO FORCE ROW LEVEL SECURITY
60 CLUSTER ON index_name
61 SET WITHOUT CLUSTER
62 SET WITHOUT OIDS
63 SET TABLESPACE new_tablespace
64 SET { LOGGED | UNLOGGED }
65 SET ( storage_parameter [= value] [, ... ] )
66 RESET ( storage_parameter [, ... ] )
67 INHERIT parent_table
68 NO INHERIT parent_table
69 OF type_name
70 NOT OF
71 OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
72 REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
73 and partition_bound_spec is:
75 IN ( partition_bound_expr [, ...] ) |
77 FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )
78 TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |
79 WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )
80 and column_constraint is:
82 [ CONSTRAINT constraint_name ]
84 { NOT NULL |
85 NULL |
86 CHECK ( expression ) [ NO INHERIT ] |
87 DEFAULT default_expr |
88 GENERATED ALWAYS AS ( generation_expr ) STORED |
89 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
90 UNIQUE index_parameters |
91 PRIMARY KEY index_parameters |
92 REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
93 [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
94 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
95 and table_constraint is:
97 [ CONSTRAINT constraint_name ]
99 { CHECK ( expression ) [ NO INHERIT ] |
100 UNIQUE ( column_name [, ... ] ) index_parameters |
101 PRIMARY KEY ( column_name [, ... ] ) index_parameters |
102 EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
103 FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
104 [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }
105 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
106 and table_constraint_using_index is:
108 [ CONSTRAINT constraint_name ]
110 { UNIQUE | PRIMARY KEY } USING INDEX index_name
111 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
112 index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
114 [ INCLUDE ( column_name [, ... ] ) ]
116 [ WITH ( storage_parameter [= value] [, ... ] ) ]
117 [ USING INDEX TABLESPACE tablespace_name ]
118 exclude_element in an EXCLUDE constraint is:
120 { column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
122
124 ALTER TABLE changes the definition of an existing table. There are
125 several subforms described below. Note that the lock level required may
126 differ for each subform. An ACCESS EXCLUSIVE lock is acquired unless
127 explicitly noted. When multiple subcommands are given, the lock
128 acquired will be the strictest one required by any subcommand.
129 ADD COLUMN [ IF NOT EXISTS ]
131 This form adds a new column to the table, using the same syntax as
132 CREATE TABLE. If IF NOT EXISTS is specified and a column already
133 exists with this name, no error is thrown.
134 DROP COLUMN [ IF EXISTS ]
136 This form drops a column from a table. Indexes and table
137 constraints involving the column will be automatically dropped as
138 well. Multivariate statistics referencing the dropped column will
139 also be removed if the removal of the column would cause the
140 statistics to contain data for only a single column. You will need
141 to say CASCADE if anything outside the table depends on the column,
142 for example, foreign key references or views. If IF EXISTS is
143 specified and the column does not exist, no error is thrown. In
144 this case a notice is issued instead.
145 SET DATA TYPE
147 This form changes the type of a column of a table. Indexes and
148 simple table constraints involving the column will be automatically
149 converted to use the new column type by reparsing the originally
150 supplied expression. The optional COLLATE clause specifies a
151 collation for the new column; if omitted, the collation is the
152 default for the new column type. The optional USING clause
153 specifies how to compute the new column value from the old; if
154 omitted, the default conversion is the same as an assignment cast
155 from old data type to new. A USING clause must be provided if there
156 is no implicit or assignment cast from old to new type.
157 SET/DROP DEFAULT
159 These forms set or remove the default value for a column (where
160 removal is equivalent to setting the default value to NULL). The
161 new default value will only apply in subsequent INSERT or UPDATE
162 commands; it does not cause rows already in the table to change.
163 SET/DROP NOT NULL
165 These forms change whether a column is marked to allow null values
166 or to reject null values.
167 SET NOT NULL may only be applied to a column provided none of the
169 records in the table contain a NULL value for the column.
170 Ordinarily this is checked during the ALTER TABLE by scanning the
171 entire table; however, if a valid CHECK constraint is found which
172 proves no NULL can exist, then the table scan is skipped.
173 If this table is a partition, one cannot perform DROP NOT NULL on a
175 column if it is marked NOT NULL in the parent table. To drop the
176 NOT NULL constraint from all the partitions, perform DROP NOT NULL
177 on the parent table. Even if there is no NOT NULL constraint on the
178 parent, such a constraint can still be added to individual
179 partitions, if desired; that is, the children can disallow nulls
180 even if the parent allows them, but not the other way around.
181 DROP EXPRESSION [ IF EXISTS ]
183 This form turns a stored generated column into a normal base
184 column. Existing data in the columns is retained, but future
185 changes will no longer apply the generation expression.
186 If DROP EXPRESSION IF EXISTS is specified and the column is not a
188 stored generated column, no error is thrown. In this case a notice
189 is issued instead.
190 ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
192 SET GENERATED { ALWAYS | BY DEFAULT }
193 DROP IDENTITY [ IF EXISTS ]
194 These forms change whether a column is an identity column or change
195 the generation attribute of an existing identity column. See CREATE
196 TABLE for details. Like SET DEFAULT, these forms only affect the
197 behavior of subsequent INSERT and UPDATE commands; they do not
198 cause rows already in the table to change.
199 If DROP IDENTITY IF EXISTS is specified and the column is not an
201 identity column, no error is thrown. In this case a notice is
202 issued instead.
203 SET sequence_option
205 RESTART
206 These forms alter the sequence that underlies an existing identity
207 column. sequence_option is an option supported by ALTER SEQUENCE
208 such as INCREMENT BY.
209 SET STATISTICS
211 This form sets the per-column statistics-gathering target for
212 subsequent ANALYZE operations. The target can be set in the range 0
213 to 10000; alternatively, set it to -1 to revert to using the system
214 default statistics target (default_statistics_target). For more
215 information on the use of statistics by the PostgreSQL query
216 planner, refer to Section 14.2.
217 SET STATISTICS acquires a SHARE UPDATE EXCLUSIVE lock.
219 SET ( attribute_option = value [, ... ] )
221 RESET ( attribute_option [, ... ] )
222 This form sets or resets per-attribute options. Currently, the only
223 defined per-attribute options are n_distinct and
224 n_distinct_inherited, which override the number-of-distinct-values
225 estimates made by subsequent ANALYZE operations. n_distinct
226 affects the statistics for the table itself, while
227 n_distinct_inherited affects the statistics gathered for the table
228 plus its inheritance children. When set to a positive value,
229 ANALYZE will assume that the column contains exactly the specified
230 number of distinct nonnull values. When set to a negative value,
231 which must be greater than or equal to -1, ANALYZE will assume that
232 the number of distinct nonnull values in the column is linear in
233 the size of the table; the exact count is to be computed by
234 multiplying the estimated table size by the absolute value of the
235 given number. For example, a value of -1 implies that all values in
236 the column are distinct, while a value of -0.5 implies that each
237 value appears twice on the average. This can be useful when the
238 size of the table changes over time, since the multiplication by
239 the number of rows in the table is not performed until query
240 planning time. Specify a value of 0 to revert to estimating the
241 number of distinct values normally. For more information on the use
242 of statistics by the PostgreSQL query planner, refer to
243 Section 14.2.
244 Changing per-attribute options acquires a SHARE UPDATE EXCLUSIVE
246 lock.
247 SET STORAGE
249 This form sets the storage mode for a column. This controls whether
250 this column is held inline or in a secondary TOAST table, and
251 whether the data should be compressed or not. PLAIN must be used
252 for fixed-length values such as integer and is inline,
253 uncompressed. MAIN is for inline, compressible data. EXTERNAL is
254 for external, uncompressed data, and EXTENDED is for external,
255 compressed data. EXTENDED is the default for most data types that
256 support non-PLAIN storage. Use of EXTERNAL will make substring
257 operations on very large text and bytea values run faster, at the
258 penalty of increased storage space. Note that SET STORAGE doesn't
259 itself change anything in the table, it just sets the strategy to
260 be pursued during future table updates. See Section 70.2 for more
261 information.
262 SET COMPRESSION compression_method
264 This form sets the compression method for a column, determining how
265 values inserted in future will be compressed (if the storage mode
266 permits compression at all). This does not cause the table to be
267 rewritten, so existing data may still be compressed with other
268 compression methods. If the table is restored with pg_restore, then
269 all values are rewritten with the configured compression method.
270 However, when data is inserted from another relation (for example,
271 by INSERT ... SELECT), values from the source table are not
272 necessarily detoasted, so any previously compressed data may retain
273 its existing compression method, rather than being recompressed
274 with the compression method of the target column. The supported
275 compression methods are pglz and lz4. (lz4 is available only if
276 --with-lz4 was used when building PostgreSQL.) In addition,
277 compression_method can be default, which selects the default
278 behavior of consulting the default_toast_compression setting at the
279 time of data insertion to determine the method to use.
280 ADD table_constraint [ NOT VALID ]
282 This form adds a new constraint to a table using the same
283 constraint syntax as CREATE TABLE, plus the option NOT VALID, which
284 is currently only allowed for foreign key and CHECK constraints.
285 Normally, this form will cause a scan of the table to verify that
287 all existing rows in the table satisfy the new constraint. But if
288 the NOT VALID option is used, this potentially-lengthy scan is
289 skipped. The constraint will still be enforced against subsequent
290 inserts or updates (that is, they'll fail unless there is a
291 matching row in the referenced table, in the case of foreign keys,
292 or they'll fail unless the new row matches the specified check
293 condition). But the database will not assume that the constraint
294 holds for all rows in the table, until it is validated by using the
295 VALIDATE CONSTRAINT option. See Notes below for more information
296 about using the NOT VALID option.
297 Although most forms of ADD table_constraint require an ACCESS
299 EXCLUSIVE lock, ADD FOREIGN KEY requires only a SHARE ROW EXCLUSIVE
300 lock. Note that ADD FOREIGN KEY also acquires a SHARE ROW EXCLUSIVE
301 lock on the referenced table, in addition to the lock on the table
302 on which the constraint is declared.
303 Additional restrictions apply when unique or primary key
305 constraints are added to partitioned tables; see CREATE TABLE.
306 Also, foreign key constraints on partitioned tables may not be
307 declared NOT VALID at present.
308 ADD table_constraint_using_index
310 This form adds a new PRIMARY KEY or UNIQUE constraint to a table
311 based on an existing unique index. All the columns of the index
312 will be included in the constraint.
313 The index cannot have expression columns nor be a partial index.
315 Also, it must be a b-tree index with default sort ordering. These
316 restrictions ensure that the index is equivalent to one that would
317 be built by a regular ADD PRIMARY KEY or ADD UNIQUE command.
318 If PRIMARY KEY is specified, and the index's columns are not
320 already marked NOT NULL, then this command will attempt to do ALTER
321 COLUMN SET NOT NULL against each such column. That requires a full
322 table scan to verify the column(s) contain no nulls. In all other
323 cases, this is a fast operation.
324 If a constraint name is provided then the index will be renamed to
326 match the constraint name. Otherwise the constraint will be named
327 the same as the index.
328 After this command is executed, the index is “owned” by the
330 constraint, in the same way as if the index had been built by a
331 regular ADD PRIMARY KEY or ADD UNIQUE command. In particular,
332 dropping the constraint will make the index disappear too.
333 This form is not currently supported on partitioned tables.
335 Note
337 Adding a constraint using an existing index can be helpful in
338 situations where a new constraint needs to be added without
339 blocking table updates for a long time. To do that, create the
340 index using CREATE INDEX CONCURRENTLY, and then install it as
341 an official constraint using this syntax. See the example
342 below.
343 ALTER CONSTRAINT
345 This form alters the attributes of a constraint that was previously
346 created. Currently only foreign key constraints may be altered.
347 VALIDATE CONSTRAINT
349 This form validates a foreign key or check constraint that was
350 previously created as NOT VALID, by scanning the table to ensure
351 there are no rows for which the constraint is not satisfied.
352 Nothing happens if the constraint is already marked valid. (See
353 Notes below for an explanation of the usefulness of this command.)
354 This command acquires a SHARE UPDATE EXCLUSIVE lock.
356 DROP CONSTRAINT [ IF EXISTS ]
358 This form drops the specified constraint on a table, along with any
359 index underlying the constraint. If IF EXISTS is specified and the
360 constraint does not exist, no error is thrown. In this case a
361 notice is issued instead.
362 DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER
364 These forms configure the firing of trigger(s) belonging to the
365 table. A disabled trigger is still known to the system, but is not
366 executed when its triggering event occurs. For a deferred trigger,
367 the enable status is checked when the event occurs, not when the
368 trigger function is actually executed. One can disable or enable a
369 single trigger specified by name, or all triggers on the table, or
370 only user triggers (this option excludes internally generated
371 constraint triggers such as those that are used to implement
372 foreign key constraints or deferrable uniqueness and exclusion
373 constraints). Disabling or enabling internally generated constraint
374 triggers requires superuser privileges; it should be done with
375 caution since of course the integrity of the constraint cannot be
376 guaranteed if the triggers are not executed.
377 The trigger firing mechanism is also affected by the configuration
379 variable session_replication_role. Simply enabled triggers (the
380 default) will fire when the replication role is “origin” (the
381 default) or “local”. Triggers configured as ENABLE REPLICA will
382 only fire if the session is in “replica” mode, and triggers
383 configured as ENABLE ALWAYS will fire regardless of the current
384 replication role.
385 The effect of this mechanism is that in the default configuration,
387 triggers do not fire on replicas. This is useful because if a
388 trigger is used on the origin to propagate data between tables,
389 then the replication system will also replicate the propagated
390 data, and the trigger should not fire a second time on the replica,
391 because that would lead to duplication. However, if a trigger is
392 used for another purpose such as creating external alerts, then it
393 might be appropriate to set it to ENABLE ALWAYS so that it is also
394 fired on replicas.
395 This command acquires a SHARE ROW EXCLUSIVE lock.
397 DISABLE/ENABLE [ REPLICA | ALWAYS ] RULE
399 These forms configure the firing of rewrite rules belonging to the
400 table. A disabled rule is still known to the system, but is not
401 applied during query rewriting. The semantics are as for
402 disabled/enabled triggers. This configuration is ignored for ON
403 SELECT rules, which are always applied in order to keep views
404 working even if the current session is in a non-default replication
405 role.
406 The rule firing mechanism is also affected by the configuration
408 variable session_replication_role, analogous to triggers as
409 described above.
410 DISABLE/ENABLE ROW LEVEL SECURITY
412 These forms control the application of row security policies
413 belonging to the table. If enabled and no policies exist for the
414 table, then a default-deny policy is applied. Note that policies
415 can exist for a table even if row-level security is disabled. In
416 this case, the policies will not be applied and the policies will
417 be ignored. See also CREATE POLICY.
418 NO FORCE/FORCE ROW LEVEL SECURITY
420 These forms control the application of row security policies
421 belonging to the table when the user is the table owner. If
422 enabled, row-level security policies will be applied when the user
423 is the table owner. If disabled (the default) then row-level
424 security will not be applied when the user is the table owner. See
425 also CREATE POLICY.
426 CLUSTER ON
428 This form selects the default index for future CLUSTER operations.
429 It does not actually re-cluster the table.
430 Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
432 SET WITHOUT CLUSTER
434 This form removes the most recently used CLUSTER index
435 specification from the table. This affects future cluster
436 operations that don't specify an index.
437 Changing cluster options acquires a SHARE UPDATE EXCLUSIVE lock.
439 SET WITHOUT OIDS
441 Backward-compatible syntax for removing the oid system column. As
442 oid system columns cannot be added anymore, this never has an
443 effect.
444 SET TABLESPACE
446 This form changes the table's tablespace to the specified
447 tablespace and moves the data file(s) associated with the table to
448 the new tablespace. Indexes on the table, if any, are not moved;
449 but they can be moved separately with additional SET TABLESPACE
450 commands. When applied to a partitioned table, nothing is moved,
451 but any partitions created afterwards with CREATE TABLE PARTITION
452 OF will use that tablespace, unless overridden by a TABLESPACE
453 clause.
454 All tables in the current database in a tablespace can be moved by
456 using the ALL IN TABLESPACE form, which will lock all tables to be
457 moved first and then move each one. This form also supports OWNED
458 BY, which will only move tables owned by the roles specified. If
459 the NOWAIT option is specified then the command will fail if it is
460 unable to acquire all of the locks required immediately. Note that
461 system catalogs are not moved by this command; use ALTER DATABASE
462 or explicit ALTER TABLE invocations instead if desired. The
463 information_schema relations are not considered part of the system
464 catalogs and will be moved. See also CREATE TABLESPACE.
465 SET { LOGGED | UNLOGGED }
467 This form changes the table from unlogged to logged or vice-versa
468 (see UNLOGGED). It cannot be applied to a temporary table.
469 SET ( storage_parameter [= value] [, ... ] )
471 This form changes one or more storage parameters for the table. See
472 Storage Parameters in the CREATE TABLE documentation for details on
473 the available parameters. Note that the table contents will not be
474 modified immediately by this command; depending on the parameter
475 you might need to rewrite the table to get the desired effects.
476 That can be done with VACUUM FULL, CLUSTER or one of the forms of
477 ALTER TABLE that forces a table rewrite. For planner related
478 parameters, changes will take effect from the next time the table
479 is locked so currently executing queries will not be affected.
480 SHARE UPDATE EXCLUSIVE lock will be taken for fillfactor, toast and
482 autovacuum storage parameters, as well as the planner parameter
483 parallel_workers.
484 RESET ( storage_parameter [, ... ] )
486 This form resets one or more storage parameters to their defaults.
487 As with SET, a table rewrite might be needed to update the table
488 entirely.
489 INHERIT parent_table
491 This form adds the target table as a new child of the specified
492 parent table. Subsequently, queries against the parent will include
493 records of the target table. To be added as a child, the target
494 table must already contain all the same columns as the parent (it
495 could have additional columns, too). The columns must have matching
496 data types, and if they have NOT NULL constraints in the parent
497 then they must also have NOT NULL constraints in the child.
498 There must also be matching child-table constraints for all CHECK
500 constraints of the parent, except those marked non-inheritable
501 (that is, created with ALTER TABLE ... ADD CONSTRAINT ... NO
502 INHERIT) in the parent, which are ignored; all child-table
503 constraints matched must not be marked non-inheritable. Currently
504 UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints are not
505 considered, but this might change in the future.
506 NO INHERIT parent_table
508 This form removes the target table from the list of children of the
509 specified parent table. Queries against the parent table will no
510 longer include records drawn from the target table.
511 OF type_name
513 This form links the table to a composite type as though CREATE
514 TABLE OF had formed it. The table's list of column names and types
515 must precisely match that of the composite type. The table must not
516 inherit from any other table. These restrictions ensure that CREATE
517 TABLE OF would permit an equivalent table definition.
518 NOT OF
520 This form dissociates a typed table from its type.
521 OWNER TO
523 This form changes the owner of the table, sequence, view,
524 materialized view, or foreign table to the specified user.
525 REPLICA IDENTITY
527 This form changes the information which is written to the
528 write-ahead log to identify rows which are updated or deleted. In
529 most cases, the old value of each column is only logged if it
530 differs from the new value; however, if the old value is stored
531 externally, it is always logged regardless of whether it changed.
532 This option has no effect except when logical replication is in
533 use.
534 DEFAULT
536 Records the old values of the columns of the primary key, if
537 any. This is the default for non-system tables.
538 USING INDEX index_name
540 Records the old values of the columns covered by the named
541 index, that must be unique, not partial, not deferrable, and
542 include only columns marked NOT NULL. If this index is dropped,
543 the behavior is the same as NOTHING.
544 FULL
546 Records the old values of all columns in the row.
547 NOTHING
549 Records no information about the old row. This is the default
550 for system tables.
551 RENAME
554 The RENAME forms change the name of a table (or an index, sequence,
555 view, materialized view, or foreign table), the name of an
556 individual column in a table, or the name of a constraint of the
557 table. When renaming a constraint that has an underlying index, the
558 index is renamed as well. There is no effect on the stored data.
559 SET SCHEMA
561 This form moves the table into another schema. Associated indexes,
562 constraints, and sequences owned by table columns are moved as
563 well.
564 ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec |
566 DEFAULT }
567 This form attaches an existing table (which might itself be
568 partitioned) as a partition of the target table. The table can be
569 attached as a partition for specific values using FOR VALUES or as
570 a default partition by using DEFAULT. For each index in the target
571 table, a corresponding one will be created in the attached table;
572 or, if an equivalent index already exists, it will be attached to
573 the target table's index, as if ALTER INDEX ATTACH PARTITION had
574 been executed. Note that if the existing table is a foreign table,
575 it is currently not allowed to attach the table as a partition of
576 the target table if there are UNIQUE indexes on the target table.
577 (See also CREATE FOREIGN TABLE (CREATE_FOREIGN_TABLE(7)).) For each
578 user-defined row-level trigger that exists in the target table, a
579 corresponding one is created in the attached table.
580 A partition using FOR VALUES uses same syntax for
582 partition_bound_spec as CREATE TABLE. The partition bound
583 specification must correspond to the partitioning strategy and
584 partition key of the target table. The table to be attached must
585 have all the same columns as the target table and no more;
586 moreover, the column types must also match. Also, it must have all
587 the NOT NULL and CHECK constraints of the target table. Currently
588 FOREIGN KEY constraints are not considered. UNIQUE and PRIMARY KEY
589 constraints from the parent table will be created in the partition,
590 if they don't already exist. If any of the CHECK constraints of the
591 table being attached are marked NO INHERIT, the command will fail;
592 such constraints must be recreated without the NO INHERIT clause.
593 If the new partition is a regular table, a full table scan is
595 performed to check that existing rows in the table do not violate
596 the partition constraint. It is possible to avoid this scan by
597 adding a valid CHECK constraint to the table that allows only rows
598 satisfying the desired partition constraint before running this
599 command. The CHECK constraint will be used to determine that the
600 table need not be scanned to validate the partition constraint.
601 This does not work, however, if any of the partition keys is an
602 expression and the partition does not accept NULL values. If
603 attaching a list partition that will not accept NULL values, also
604 add NOT NULL constraint to the partition key column, unless it's an
605 expression.
606 If the new partition is a foreign table, nothing is done to verify
608 that all the rows in the foreign table obey the partition
609 constraint. (See the discussion in CREATE FOREIGN TABLE
610 (CREATE_FOREIGN_TABLE(7)) about constraints on the foreign table.)
611 When a table has a default partition, defining a new partition
613 changes the partition constraint for the default partition. The
614 default partition can't contain any rows that would need to be
615 moved to the new partition, and will be scanned to verify that none
616 are present. This scan, like the scan of the new partition, can be
617 avoided if an appropriate CHECK constraint is present. Also like
618 the scan of the new partition, it is always skipped when the
619 default partition is a foreign table.
620 Attaching a partition acquires a SHARE UPDATE EXCLUSIVE lock on the
622 parent table, in addition to the ACCESS EXCLUSIVE locks on the
623 table being attached and on the default partition (if any).
624 Further locks must also be held on all sub-partitions if the table
626 being attached is itself a partitioned table. Likewise if the
627 default partition is itself a partitioned table. The locking of the
628 sub-partitions can be avoided by adding a CHECK constraint as
629 described in Section 5.11.2.2.
630 DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]
632 This form detaches the specified partition of the target table. The
633 detached partition continues to exist as a standalone table, but no
634 longer has any ties to the table from which it was detached. Any
635 indexes that were attached to the target table's indexes are
636 detached. Any triggers that were created as clones of those in the
637 target table are removed. SHARE lock is obtained on any tables
638 that reference this partitioned table in foreign key constraints.
639 If CONCURRENTLY is specified, it runs using a reduced lock level to
641 avoid blocking other sessions that might be accessing the
642 partitioned table. In this mode, two transactions are used
643 internally. During the first transaction, a SHARE UPDATE EXCLUSIVE
644 lock is taken on both parent table and partition, and the partition
645 is marked as undergoing detach; at that point, the transaction is
646 committed and all other transactions using the partitioned table
647 are waited for. Once all those transactions have completed, the
648 second transaction acquires SHARE UPDATE EXCLUSIVE on the
649 partitioned table and ACCESS EXCLUSIVE on the partition, and the
650 detach process completes. A CHECK constraint that duplicates the
651 partition constraint is added to the partition. CONCURRENTLY
652 cannot be run in a transaction block and is not allowed if the
653 partitioned table contains a default partition.
654 If FINALIZE is specified, a previous DETACH CONCURRENTLY invocation
656 that was canceled or interrupted is completed. At most one
657 partition in a partitioned table can be pending detach at a time.
658 All the forms of ALTER TABLE that act on a single table, except RENAME,
660 SET SCHEMA, ATTACH PARTITION, and DETACH PARTITION can be combined into
661 a list of multiple alterations to be applied together. For example, it
662 is possible to add several columns and/or alter the type of several
663 columns in a single command. This is particularly useful with large
664 tables, since only one pass over the table need be made.
665 You must own the table to use ALTER TABLE. To change the schema or
667 tablespace of a table, you must also have CREATE privilege on the new
668 schema or tablespace. To add the table as a new child of a parent
669 table, you must own the parent table as well. Also, to attach a table
670 as a new partition of the table, you must own the table being attached.
671 To alter the owner, you must also be a direct or indirect member of the
672 new owning role, and that role must have CREATE privilege on the
673 table's schema. (These restrictions enforce that altering the owner
674 doesn't do anything you couldn't do by dropping and recreating the
675 table. However, a superuser can alter ownership of any table anyway.)
676 To add a column or alter a column type or use the OF clause, you must
677 also have USAGE privilege on the data type.
678
680 IF EXISTS
681 Do not throw an error if the table does not exist. A notice is
682 issued in this case.
683 name
685 The name (optionally schema-qualified) of an existing table to
686 alter. If ONLY is specified before the table name, only that table
687 is altered. If ONLY is not specified, the table and all its
688 descendant tables (if any) are altered. Optionally, * can be
689 specified after the table name to explicitly indicate that
690 descendant tables are included.
691 column_name
693 Name of a new or existing column.
694 new_column_name
696 New name for an existing column.
697 new_name
699 New name for the table.
700 data_type
702 Data type of the new column, or new data type for an existing
703 column.
704 table_constraint
706 New table constraint for the table.
707 constraint_name
709 Name of a new or existing constraint.
710 CASCADE
712 Automatically drop objects that depend on the dropped column or
713 constraint (for example, views referencing the column), and in turn
714 all objects that depend on those objects (see Section 5.14).
715 RESTRICT
717 Refuse to drop the column or constraint if there are any dependent
718 objects. This is the default behavior.
719 trigger_name
721 Name of a single trigger to disable or enable.
722 ALL
724 Disable or enable all triggers belonging to the table. (This
725 requires superuser privilege if any of the triggers are internally
726 generated constraint triggers such as those that are used to
727 implement foreign key constraints or deferrable uniqueness and
728 exclusion constraints.)
729 USER
731 Disable or enable all triggers belonging to the table except for
732 internally generated constraint triggers such as those that are
733 used to implement foreign key constraints or deferrable uniqueness
734 and exclusion constraints.
735 index_name
737 The name of an existing index.
738 storage_parameter
740 The name of a table storage parameter.
741 value
743 The new value for a table storage parameter. This might be a number
744 or a word depending on the parameter.
745 parent_table
747 A parent table to associate or de-associate with this table.
748 new_owner
750 The user name of the new owner of the table.
751 new_tablespace
753 The name of the tablespace to which the table will be moved.
754 new_schema
756 The name of the schema to which the table will be moved.
757 partition_name
759 The name of the table to attach as a new partition or to detach
760 from this table.
761 partition_bound_spec
763 The partition bound specification for a new partition. Refer to
764 CREATE TABLE (CREATE_TABLE(7)) for more details on the syntax of
765 the same.
766
768 The key word COLUMN is noise and can be omitted.
769 When a column is added with ADD COLUMN and a non-volatile DEFAULT is
771 specified, the default is evaluated at the time of the statement and
772 the result stored in the table's metadata. That value will be used for
773 the column for all existing rows. If no DEFAULT is specified, NULL is
774 used. In neither case is a rewrite of the table required.
775 Adding a column with a volatile DEFAULT or changing the type of an
777 existing column will require the entire table and its indexes to be
778 rewritten. As an exception, when changing the type of an existing
779 column, if the USING clause does not change the column contents and the
780 old type is either binary coercible to the new type or an unconstrained
781 domain over the new type, a table rewrite is not needed; but any
782 indexes on the affected columns must still be rebuilt. Table and/or
783 index rebuilds may take a significant amount of time for a large table;
784 and will temporarily require as much as double the disk space.
785 Adding a CHECK or NOT NULL constraint requires scanning the table to
787 verify that existing rows meet the constraint, but does not require a
788 table rewrite.
789 Similarly, when attaching a new partition it may be scanned to verify
791 that existing rows meet the partition constraint.
792 The main reason for providing the option to specify multiple changes in
794 a single ALTER TABLE is that multiple table scans or rewrites can
795 thereby be combined into a single pass over the table.
796 Scanning a large table to verify a new foreign key or check constraint
798 can take a long time, and other updates to the table are locked out
799 until the ALTER TABLE ADD CONSTRAINT command is committed. The main
800 purpose of the NOT VALID constraint option is to reduce the impact of
801 adding a constraint on concurrent updates. With NOT VALID, the ADD
802 CONSTRAINT command does not scan the table and can be committed
803 immediately. After that, a VALIDATE CONSTRAINT command can be issued to
804 verify that existing rows satisfy the constraint. The validation step
805 does not need to lock out concurrent updates, since it knows that other
806 transactions will be enforcing the constraint for rows that they insert
807 or update; only pre-existing rows need to be checked. Hence, validation
808 acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered.
809 (If the constraint is a foreign key then a ROW SHARE lock is also
810 required on the table referenced by the constraint.) In addition to
811 improving concurrency, it can be useful to use NOT VALID and VALIDATE
812 CONSTRAINT in cases where the table is known to contain pre-existing
813 violations. Once the constraint is in place, no new violations can be
814 inserted, and the existing problems can be corrected at leisure until
815 VALIDATE CONSTRAINT finally succeeds.
816 The DROP COLUMN form does not physically remove the column, but simply
818 makes it invisible to SQL operations. Subsequent insert and update
819 operations in the table will store a null value for the column. Thus,
820 dropping a column is quick but it will not immediately reduce the
821 on-disk size of your table, as the space occupied by the dropped column
822 is not reclaimed. The space will be reclaimed over time as existing
823 rows are updated.
824 To force immediate reclamation of space occupied by a dropped column,
826 you can execute one of the forms of ALTER TABLE that performs a rewrite
827 of the whole table. This results in reconstructing each row with the
828 dropped column replaced by a null value.
829 The rewriting forms of ALTER TABLE are not MVCC-safe. After a table
831 rewrite, the table will appear empty to concurrent transactions, if
832 they are using a snapshot taken before the rewrite occurred. See
833 Section 13.5 for more details.
834 The USING option of SET DATA TYPE can actually specify any expression
836 involving the old values of the row; that is, it can refer to other
837 columns as well as the one being converted. This allows very general
838 conversions to be done with the SET DATA TYPE syntax. Because of this
839 flexibility, the USING expression is not applied to the column's
840 default value (if any); the result might not be a constant expression
841 as required for a default. This means that when there is no implicit or
842 assignment cast from old to new type, SET DATA TYPE might fail to
843 convert the default even though a USING clause is supplied. In such
844 cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and
845 then use SET DEFAULT to add a suitable new default. Similar
846 considerations apply to indexes and constraints involving the column.
847 If a table has any descendant tables, it is not permitted to add,
849 rename, or change the type of a column in the parent table without
850 doing the same to the descendants. This ensures that the descendants
851 always have columns matching the parent. Similarly, a CHECK constraint
852 cannot be renamed in the parent without also renaming it in all
853 descendants, so that CHECK constraints also match between the parent
854 and its descendants. (That restriction does not apply to index-based
855 constraints, however.) Also, because selecting from the parent also
856 selects from its descendants, a constraint on the parent cannot be
857 marked valid unless it is also marked valid for those descendants. In
858 all of these cases, ALTER TABLE ONLY will be rejected.
859 A recursive DROP COLUMN operation will remove a descendant table's
861 column only if the descendant does not inherit that column from any
862 other parents and never had an independent definition of the column. A
863 nonrecursive DROP COLUMN (i.e., ALTER TABLE ONLY ... DROP COLUMN) never
864 removes any descendant columns, but instead marks them as independently
865 defined rather than inherited. A nonrecursive DROP COLUMN command will
866 fail for a partitioned table, because all partitions of a table must
867 have the same columns as the partitioning root.
868 The actions for identity columns (ADD GENERATED, SET etc., DROP
870 IDENTITY), as well as the actions TRIGGER, CLUSTER, OWNER, and
871 TABLESPACE never recurse to descendant tables; that is, they always act
872 as though ONLY were specified. Adding a constraint recurses only for
873 CHECK constraints that are not marked NO INHERIT.
874 Changing any part of a system catalog table is not permitted.
876 Refer to CREATE TABLE (CREATE_TABLE(7)) for a further description of
878 valid parameters. Chapter 5 has further information on inheritance.
879
881 To add a column of type varchar to a table:
882 ALTER TABLE distributors ADD COLUMN address varchar(30);
884 That will cause all existing rows in the table to be filled with null
886 values for the new column.
887 To add a column with a non-null default:
889 ALTER TABLE measurements
891 ADD COLUMN mtime timestamp with time zone DEFAULT now();
892 Existing rows will be filled with the current time as the value of the
894 new column, and then new rows will receive the time of their insertion.
895 To add a column and fill it with a value different from the default to
897 be used later:
898 ALTER TABLE transactions
900 ADD COLUMN status varchar(30) DEFAULT 'old',
901 ALTER COLUMN status SET default 'current';
902 Existing rows will be filled with old, but then the default for
904 subsequent commands will be current. The effects are the same as if the
905 two sub-commands had been issued in separate ALTER TABLE commands.
906 To drop a column from a table:
908 ALTER TABLE distributors DROP COLUMN address RESTRICT;
910 To change the types of two existing columns in one operation:
912 ALTER TABLE distributors
914 ALTER COLUMN address TYPE varchar(80),
915 ALTER COLUMN name TYPE varchar(100);
916 To change an integer column containing Unix timestamps to timestamp
918 with time zone via a USING clause:
919 ALTER TABLE foo
921 ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
922 USING
923 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
924 The same, when the column has a default expression that won't
926 automatically cast to the new data type:
927 ALTER TABLE foo
929 ALTER COLUMN foo_timestamp DROP DEFAULT,
930 ALTER COLUMN foo_timestamp TYPE timestamp with time zone
931 USING
932 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
933 ALTER COLUMN foo_timestamp SET DEFAULT now();
934 To rename an existing column:
936 ALTER TABLE distributors RENAME COLUMN address TO city;
938 To rename an existing table:
940 ALTER TABLE distributors RENAME TO suppliers;
942 To rename an existing constraint:
944 ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
946 To add a not-null constraint to a column:
948 ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
950 To remove a not-null constraint from a column:
952 ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
954 To add a check constraint to a table and all its children:
956 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
958 To add a check constraint only to a table and not to its children:
960 ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;
962 (The check constraint will not be inherited by future children,
964 either.)
965 To remove a check constraint from a table and all its children:
967 ALTER TABLE distributors DROP CONSTRAINT zipchk;
969 To remove a check constraint from one table only:
971 ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;
973 (The check constraint remains in place for any child tables.)
975 To add a foreign key constraint to a table:
977 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);
979 To add a foreign key constraint to a table with the least impact on
981 other work:
982 ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
984 ALTER TABLE distributors VALIDATE CONSTRAINT distfk;
985 To add a (multicolumn) unique constraint to a table:
987 ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
989 To add an automatically named primary key constraint to a table, noting
991 that a table can only ever have one primary key:
992 ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
994 To move a table to a different tablespace:
996 ALTER TABLE distributors SET TABLESPACE fasttablespace;
998 To move a table to a different schema:
1000 ALTER TABLE myschema.distributors SET SCHEMA yourschema;
1002 To recreate a primary key constraint, without blocking updates while
1004 the index is rebuilt:
1005 CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
1007 ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
1008 ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;
1009 To attach a partition to a range-partitioned table:
1011 ALTER TABLE measurement
1013 ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
1014 To attach a partition to a list-partitioned table:
1016 ALTER TABLE cities
1018 ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
1019 To attach a partition to a hash-partitioned table:
1021 ALTER TABLE orders
1023 ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
1024 To attach a default partition to a partitioned table:
1026 ALTER TABLE cities
1028 ATTACH PARTITION cities_partdef DEFAULT;
1029 To detach a partition from a partitioned table:
1031 ALTER TABLE measurement
1033 DETACH PARTITION measurement_y2015m12;
1034
1036 The forms ADD (without USING INDEX), DROP [COLUMN], DROP IDENTITY,
1037 RESTART, SET DEFAULT, SET DATA TYPE (without USING), SET GENERATED, and
1038 SET sequence_option conform with the SQL standard. The other forms are
1039 PostgreSQL extensions of the SQL standard. Also, the ability to specify
1040 more than one manipulation in a single ALTER TABLE command is an
1041 extension.
1042 ALTER TABLE DROP COLUMN can be used to drop the only column of a table,
1044 leaving a zero-column table. This is an extension of SQL, which
1045 disallows zero-column tables.
1046
1048 CREATE TABLE (CREATE_TABLE(7))
1049PostgreSQL 14.3 2022 ALTER TABLE(7)