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