1<!-- 2doc/src/sgml/ref/alter_table.sgml 3PostgreSQL documentation 4--> 5 6<refentry id="sql-altertable"> 7 <indexterm zone="sql-altertable"> 8 <primary>ALTER TABLE</primary> 9 </indexterm> 10 11 <refmeta> 12 <refentrytitle>ALTER TABLE</refentrytitle> 13 <manvolnum>7</manvolnum> 14 <refmiscinfo>SQL - Language Statements</refmiscinfo> 15 </refmeta> 16 17 <refnamediv> 18 <refname>ALTER TABLE</refname> 19 <refpurpose>change the definition of a table</refpurpose> 20 </refnamediv> 21 22 <refsynopsisdiv> 23<synopsis> 24ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ] 25 <replaceable class="parameter">action</replaceable> [, ... ] 26ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ] 27 RENAME [ COLUMN ] <replaceable class="parameter">column_name</replaceable> TO <replaceable class="parameter">new_column_name</replaceable> 28ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="parameter">name</replaceable> [ * ] 29 RENAME CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> TO <replaceable class="parameter">new_constraint_name</replaceable> 30ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> 31 RENAME TO <replaceable class="parameter">new_name</replaceable> 32ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> 33 SET SCHEMA <replaceable class="parameter">new_schema</replaceable> 34ALTER TABLE ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> [ OWNED BY <replaceable class="parameter">role_name</replaceable> [, ... ] ] 35 SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> [ NOWAIT ] 36ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> 37 ATTACH PARTITION <replaceable class="parameter">partition_name</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT } 38ALTER TABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> 39 DETACH PARTITION <replaceable class="parameter">partition_name</replaceable> 40 41<phrase>where <replaceable class="parameter">action</replaceable> is one of:</phrase> 42 43 ADD [ COLUMN ] [ IF NOT EXISTS ] <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">column_constraint</replaceable> [ ... ] ] 44 DROP [ COLUMN ] [ IF EXISTS ] <replaceable class="parameter">column_name</replaceable> [ RESTRICT | CASCADE ] 45 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> [ SET DATA ] TYPE <replaceable class="parameter">data_type</replaceable> [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ USING <replaceable class="parameter">expression</replaceable> ] 46 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET DEFAULT <replaceable class="parameter">expression</replaceable> 47 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP DEFAULT 48 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET | DROP } NOT NULL 49 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP EXPRESSION [ IF EXISTS ] 50 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] 51 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] } [...] 52 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> DROP IDENTITY [ IF EXISTS ] 53 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STATISTICS <replaceable class="parameter">integer</replaceable> 54 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET ( <replaceable class="parameter">attribute_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) 55 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> RESET ( <replaceable class="parameter">attribute_option</replaceable> [, ... ] ) 56 ALTER [ COLUMN ] <replaceable class="parameter">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } 57 ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ] 58 ADD <replaceable class="parameter">table_constraint_using_index</replaceable> 59 ALTER CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 60 VALIDATE CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> 61 DROP CONSTRAINT [ IF EXISTS ] <replaceable class="parameter">constraint_name</replaceable> [ RESTRICT | CASCADE ] 62 DISABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ] 63 ENABLE TRIGGER [ <replaceable class="parameter">trigger_name</replaceable> | ALL | USER ] 64 ENABLE REPLICA TRIGGER <replaceable class="parameter">trigger_name</replaceable> 65 ENABLE ALWAYS TRIGGER <replaceable class="parameter">trigger_name</replaceable> 66 DISABLE RULE <replaceable class="parameter">rewrite_rule_name</replaceable> 67 ENABLE RULE <replaceable class="parameter">rewrite_rule_name</replaceable> 68 ENABLE REPLICA RULE <replaceable class="parameter">rewrite_rule_name</replaceable> 69 ENABLE ALWAYS RULE <replaceable class="parameter">rewrite_rule_name</replaceable> 70 DISABLE ROW LEVEL SECURITY 71 ENABLE ROW LEVEL SECURITY 72 FORCE ROW LEVEL SECURITY 73 NO FORCE ROW LEVEL SECURITY 74 CLUSTER ON <replaceable class="parameter">index_name</replaceable> 75 SET WITHOUT CLUSTER 76 SET WITHOUT OIDS 77 SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> 78 SET { LOGGED | UNLOGGED } 79 SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) 80 RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] ) 81 INHERIT <replaceable class="parameter">parent_table</replaceable> 82 NO INHERIT <replaceable class="parameter">parent_table</replaceable> 83 OF <replaceable class="parameter">type_name</replaceable> 84 NOT OF 85 OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_USER | SESSION_USER } 86 REPLICA IDENTITY { DEFAULT | USING INDEX <replaceable class="parameter">index_name</replaceable> | FULL | NOTHING } 87 88<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase> 89 90IN ( <replaceable class="parameter">partition_bound_expr</replaceable> [, ...] ) | 91FROM ( { <replaceable class="parameter">partition_bound_expr</replaceable> | MINVALUE | MAXVALUE } [, ...] ) 92 TO ( { <replaceable class="parameter">partition_bound_expr</replaceable> | MINVALUE | MAXVALUE } [, ...] ) | 93WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REMAINDER <replaceable class="parameter">numeric_literal</replaceable> ) 94 95<phrase>and <replaceable class="parameter">column_constraint</replaceable> is:</phrase> 96 97[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] 98{ NOT NULL | 99 NULL | 100 CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] | 101 DEFAULT <replaceable>default_expr</replaceable> | 102 GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED | 103 GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] | 104 UNIQUE <replaceable class="parameter">index_parameters</replaceable> | 105 PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> | 106 REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] 107 [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] } 108[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 109 110<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase> 111 112[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] 113{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] | 114 UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> | 115 PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> | 116 EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] | 117 FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ] 118 [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] } 119[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 120 121<phrase>and <replaceable class="parameter">table_constraint_using_index</replaceable> is:</phrase> 122 123 [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ] 124 { UNIQUE | PRIMARY KEY } USING INDEX <replaceable class="parameter">index_name</replaceable> 125 [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] 126 127<phrase><replaceable class="parameter">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase> 128 129[ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) ] 130[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ] 131[ USING INDEX TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ] 132 133<phrase><replaceable class="parameter">exclude_element</replaceable> in an <literal>EXCLUDE</literal> constraint is:</phrase> 134 135{ <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ <replaceable class="parameter">opclass</replaceable> ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] 136</synopsis> 137 </refsynopsisdiv> 138 139 <refsect1> 140 <title>Description</title> 141 142 <para> 143 <command>ALTER TABLE</command> changes the definition of an existing table. 144 There are several subforms described below. Note that the lock level required 145 may differ for each subform. An <literal>ACCESS EXCLUSIVE</literal> lock is 146 acquired unless explicitly noted. When multiple subcommands are given, the 147 lock acquired will be the strictest one required by any subcommand. 148 149 <variablelist> 150 <varlistentry> 151 <term><literal>ADD COLUMN [ IF NOT EXISTS ]</literal></term> 152 <listitem> 153 <para> 154 This form adds a new column to the table, using the same syntax as 155 <xref linkend="sql-createtable"/>. If <literal>IF NOT EXISTS</literal> 156 is specified and a column already exists with this name, 157 no error is thrown. 158 </para> 159 </listitem> 160 </varlistentry> 161 162 <varlistentry> 163 <term><literal>DROP COLUMN [ IF EXISTS ]</literal></term> 164 <listitem> 165 <para> 166 This form drops a column from a table. Indexes and 167 table constraints involving the column will be automatically 168 dropped as well. 169 Multivariate statistics referencing the dropped column will also be 170 removed if the removal of the column would cause the statistics to 171 contain data for only a single column. 172 You will need to say <literal>CASCADE</literal> if anything outside the table 173 depends on the column, for example, foreign key references or views. 174 If <literal>IF EXISTS</literal> is specified and the column 175 does not exist, no error is thrown. In this case a notice 176 is issued instead. 177 </para> 178 </listitem> 179 </varlistentry> 180 181 <varlistentry> 182 <term><literal>SET DATA TYPE</literal></term> 183 <listitem> 184 <para> 185 This form changes the type of a column of a table. Indexes and 186 simple table constraints involving the column will be automatically 187 converted to use the new column type by reparsing the originally 188 supplied expression. 189 The optional <literal>COLLATE</literal> clause specifies a collation 190 for the new column; if omitted, the collation is the default for the 191 new column type. 192 The optional <literal>USING</literal> 193 clause specifies how to compute the new column value from the old; 194 if omitted, the default conversion is the same as an assignment 195 cast from old data type to new. A <literal>USING</literal> 196 clause must be provided if there is no implicit or assignment 197 cast from old to new type. 198 </para> 199 </listitem> 200 </varlistentry> 201 202 <varlistentry> 203 <term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term> 204 <listitem> 205 <para> 206 These forms set or remove the default value for a column (where 207 removal is equivalent to setting the default value to NULL). The new 208 default value will only apply in subsequent <command>INSERT</command> 209 or <command>UPDATE</command> commands; it does not cause rows already 210 in the table to change. 211 </para> 212 </listitem> 213 </varlistentry> 214 215 <varlistentry> 216 <term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term> 217 <listitem> 218 <para> 219 These forms change whether a column is marked to allow null 220 values or to reject null values. 221 </para> 222 223 <para> 224 <literal>SET NOT NULL</literal> may only be applied to a column 225 provided none of the records in the table contain a 226 <literal>NULL</literal> value for the column. Ordinarily this is 227 checked during the <literal>ALTER TABLE</literal> by scanning the 228 entire table; however, if a valid <literal>CHECK</literal> constraint is 229 found which proves no <literal>NULL</literal> can exist, then the 230 table scan is skipped. 231 </para> 232 233 <para> 234 If this table is a partition, one cannot perform <literal>DROP NOT NULL</literal> 235 on a column if it is marked <literal>NOT NULL</literal> in the parent 236 table. To drop the <literal>NOT NULL</literal> constraint from all the 237 partitions, perform <literal>DROP NOT NULL</literal> on the parent 238 table. Even if there is no <literal>NOT NULL</literal> constraint on the 239 parent, such a constraint can still be added to individual partitions, 240 if desired; that is, the children can disallow nulls even if the parent 241 allows them, but not the other way around. 242 </para> 243 </listitem> 244 </varlistentry> 245 246 <varlistentry> 247 <term><literal>DROP EXPRESSION [ IF EXISTS ]</literal></term> 248 <listitem> 249 <para> 250 This form turns a stored generated column into a normal base column. 251 Existing data in the columns is retained, but future changes will no 252 longer apply the generation expression. 253 </para> 254 255 <para> 256 If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the 257 column is not a stored generated column, no error is thrown. In this 258 case a notice is issued instead. 259 </para> 260 </listitem> 261 </varlistentry> 262 263 <varlistentry> 264 <term><literal>ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY</literal></term> 265 <term><literal>SET GENERATED { ALWAYS | BY DEFAULT }</literal></term> 266 <term><literal>DROP IDENTITY [ IF EXISTS ]</literal></term> 267 <listitem> 268 <para> 269 These forms change whether a column is an identity column or change the 270 generation attribute of an existing identity column. 271 See <xref linkend="sql-createtable"/> for details. 272 Like <literal>SET DEFAULT</literal>, these forms only affect the 273 behavior of subsequent <command>INSERT</command> 274 and <command>UPDATE</command> commands; they do not cause rows 275 already in the table to change. 276 </para> 277 278 <para> 279 If <literal>DROP IDENTITY IF EXISTS</literal> is specified and the 280 column is not an identity column, no error is thrown. In this case a 281 notice is issued instead. 282 </para> 283 </listitem> 284 </varlistentry> 285 286 <varlistentry> 287 <term><literal>SET <replaceable>sequence_option</replaceable></literal></term> 288 <term><literal>RESTART</literal></term> 289 <listitem> 290 <para> 291 These forms alter the sequence that underlies an existing identity 292 column. <replaceable>sequence_option</replaceable> is an option 293 supported by <xref linkend="sql-altersequence"/> such 294 as <literal>INCREMENT BY</literal>. 295 </para> 296 </listitem> 297 </varlistentry> 298 299 <varlistentry> 300 <term><literal>SET STATISTICS</literal></term> 301 <listitem> 302 <para> 303 This form 304 sets the per-column statistics-gathering target for subsequent 305 <xref linkend="sql-analyze"/> operations. 306 The target can be set in the range 0 to 10000; alternatively, set it 307 to -1 to revert to using the system default statistics 308 target (<xref linkend="guc-default-statistics-target"/>). 309 For more information on the use of statistics by the 310 <productname>PostgreSQL</productname> query planner, refer to 311 <xref linkend="planner-stats"/>. 312 </para> 313 <para> 314 <literal>SET STATISTICS</literal> acquires a 315 <literal>SHARE UPDATE EXCLUSIVE</literal> lock. 316 </para> 317 </listitem> 318 </varlistentry> 319 320 <varlistentry> 321 <term><literal>SET ( <replaceable class="parameter">attribute_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )</literal></term> 322 <term><literal>RESET ( <replaceable class="parameter">attribute_option</replaceable> [, ... ] )</literal></term> 323 <listitem> 324 <para> 325 This form sets or resets per-attribute options. Currently, the only 326 defined per-attribute options are <literal>n_distinct</literal> and 327 <literal>n_distinct_inherited</literal>, which override the 328 number-of-distinct-values estimates made by subsequent 329 <xref linkend="sql-analyze"/> 330 operations. <literal>n_distinct</literal> affects the statistics for the table 331 itself, while <literal>n_distinct_inherited</literal> affects the statistics 332 gathered for the table plus its inheritance children. When set to a 333 positive value, <command>ANALYZE</command> will assume that the column contains 334 exactly the specified number of distinct nonnull values. When set to a 335 negative value, which must be greater 336 than or equal to -1, <command>ANALYZE</command> will assume that the number of 337 distinct nonnull values in the column is linear in the size of the 338 table; the exact count is to be computed by multiplying the estimated 339 table size by the absolute value of the given number. For example, 340 a value of -1 implies that all values in the column are distinct, while 341 a value of -0.5 implies that each value appears twice on the average. 342 This can be useful when the size of the table changes over time, since 343 the multiplication by the number of rows in the table is not performed 344 until query planning time. Specify a value of 0 to revert to estimating 345 the number of distinct values normally. For more information on the use 346 of statistics by the <productname>PostgreSQL</productname> query 347 planner, refer to <xref linkend="planner-stats"/>. 348 </para> 349 <para> 350 Changing per-attribute options acquires a 351 <literal>SHARE UPDATE EXCLUSIVE</literal> lock. 352 </para> 353 </listitem> 354 </varlistentry> 355 356 <varlistentry> 357 <term> 358 <literal>SET STORAGE</literal> 359 <indexterm> 360 <primary>TOAST</primary> 361 <secondary>per-column storage settings</secondary> 362 </indexterm> 363 </term> 364 <listitem> 365 <para> 366 This form sets the storage mode for a column. This controls whether this 367 column is held inline or in a secondary <acronym>TOAST</acronym> table, and 368 whether the data 369 should be compressed or not. <literal>PLAIN</literal> must be used 370 for fixed-length values such as <type>integer</type> and is 371 inline, uncompressed. <literal>MAIN</literal> is for inline, 372 compressible data. <literal>EXTERNAL</literal> is for external, 373 uncompressed data, and <literal>EXTENDED</literal> is for external, 374 compressed data. <literal>EXTENDED</literal> is the default for most 375 data types that support non-<literal>PLAIN</literal> storage. 376 Use of <literal>EXTERNAL</literal> will make substring operations on 377 very large <type>text</type> and <type>bytea</type> values run faster, 378 at the penalty of increased storage space. Note that 379 <literal>SET STORAGE</literal> doesn't itself change anything in the table, 380 it just sets the strategy to be pursued during future table updates. 381 See <xref linkend="storage-toast"/> for more information. 382 </para> 383 </listitem> 384 </varlistentry> 385 386 <varlistentry> 387 <term><literal>ADD <replaceable class="parameter">table_constraint</replaceable> [ NOT VALID ]</literal></term> 388 <listitem> 389 <para> 390 This form adds a new constraint to a table using the same constraint 391 syntax as <xref linkend="sql-createtable"/>, plus the option <literal>NOT 392 VALID</literal>, which is currently only allowed for foreign key 393 and CHECK constraints. 394 </para> 395 396 <para> 397 Normally, this form will cause a scan of the table to verify that all 398 existing rows in the table satisfy the new constraint. But if 399 the <literal>NOT VALID</literal> option is used, this 400 potentially-lengthy scan is skipped. The constraint will still be 401 enforced against subsequent inserts or updates (that is, they'll fail 402 unless there is a matching row in the referenced table, in the case 403 of foreign keys, or they'll fail unless the new row matches the 404 specified check condition). But the 405 database will not assume that the constraint holds for all rows in 406 the table, until it is validated by using the <literal>VALIDATE 407 CONSTRAINT</literal> option. 408 See <xref linkend="sql-altertable-notes"/> below for more information 409 about using the <literal>NOT VALID</literal> option. 410 </para> 411 412 <para> 413 Although most forms of <literal>ADD 414 <replaceable class="parameter">table_constraint</replaceable></literal> 415 require an <literal>ACCESS EXCLUSIVE</literal> lock, <literal>ADD 416 FOREIGN KEY</literal> requires only a <literal>SHARE ROW 417 EXCLUSIVE</literal> lock. Note that <literal>ADD FOREIGN KEY</literal> 418 also acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock on the 419 referenced table, in addition to the lock on the table on which the 420 constraint is declared. 421 </para> 422 423 <para> 424 Additional restrictions apply when unique or primary key constraints 425 are added to partitioned tables; see <xref linkend="sql-createtable"/>. 426 Also, foreign key constraints on partitioned 427 tables may not be declared <literal>NOT VALID</literal> at present. 428 </para> 429 430 </listitem> 431 </varlistentry> 432 433 <varlistentry> 434 <term><literal>ADD <replaceable class="parameter">table_constraint_using_index</replaceable></literal></term> 435 <listitem> 436 <para> 437 This form adds a new <literal>PRIMARY KEY</literal> or <literal>UNIQUE</literal> 438 constraint to a table based on an existing unique index. All the 439 columns of the index will be included in the constraint. 440 </para> 441 442 <para> 443 The index cannot have expression columns nor be a partial index. 444 Also, it must be a b-tree index with default sort ordering. These 445 restrictions ensure that the index is equivalent to one that would be 446 built by a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal> 447 command. 448 </para> 449 450 <para> 451 If <literal>PRIMARY KEY</literal> is specified, and the index's columns are not 452 already marked <literal>NOT NULL</literal>, then this command will attempt to 453 do <literal>ALTER COLUMN SET NOT NULL</literal> against each such column. 454 That requires a full table scan to verify the column(s) contain no 455 nulls. In all other cases, this is a fast operation. 456 </para> 457 458 <para> 459 If a constraint name is provided then the index will be renamed to match 460 the constraint name. Otherwise the constraint will be named the same as 461 the index. 462 </para> 463 464 <para> 465 After this command is executed, the index is <quote>owned</quote> by the 466 constraint, in the same way as if the index had been built by 467 a regular <literal>ADD PRIMARY KEY</literal> or <literal>ADD UNIQUE</literal> 468 command. In particular, dropping the constraint will make the index 469 disappear too. 470 </para> 471 472 <para> 473 This form is not currently supported on partitioned tables. 474 </para> 475 476 <note> 477 <para> 478 Adding a constraint using an existing index can be helpful in 479 situations where a new constraint needs to be added without blocking 480 table updates for a long time. To do that, create the index using 481 <command>CREATE INDEX CONCURRENTLY</command>, and then install it as an 482 official constraint using this syntax. See the example below. 483 </para> 484 </note> 485 </listitem> 486 </varlistentry> 487 488 <varlistentry> 489 <term><literal>ALTER CONSTRAINT</literal></term> 490 <listitem> 491 <para> 492 This form alters the attributes of a constraint that was previously 493 created. Currently only foreign key constraints may be altered. 494 </para> 495 </listitem> 496 </varlistentry> 497 498 <varlistentry> 499 <term><literal>VALIDATE CONSTRAINT</literal></term> 500 <listitem> 501 <para> 502 This form validates a foreign key or check constraint that was 503 previously created as <literal>NOT VALID</literal>, by scanning the 504 table to ensure there are no rows for which the constraint is not 505 satisfied. Nothing happens if the constraint is already marked valid. 506 (See <xref linkend="sql-altertable-notes"/> below for an explanation 507 of the usefulness of this command.) 508 </para> 509 <para> 510 This command acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock. 511 </para> 512 </listitem> 513 </varlistentry> 514 515 <varlistentry> 516 <term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term> 517 <listitem> 518 <para> 519 This form drops the specified constraint on a table, along with 520 any index underlying the constraint. 521 If <literal>IF EXISTS</literal> is specified and the constraint 522 does not exist, no error is thrown. In this case a notice is issued instead. 523 </para> 524 </listitem> 525 </varlistentry> 526 527 <varlistentry> 528 <term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] TRIGGER</literal></term> 529 <listitem> 530 <para> 531 These forms configure the firing of trigger(s) belonging to the table. 532 A disabled trigger is still known to the system, but is not executed 533 when its triggering event occurs. For a deferred trigger, the enable 534 status is checked when the event occurs, not when the trigger function 535 is actually executed. One can disable or enable a single 536 trigger specified by name, or all triggers on the table, or only 537 user triggers (this option excludes internally generated constraint 538 triggers such as those that are used to implement foreign key 539 constraints or deferrable uniqueness and exclusion constraints). 540 Disabling or enabling internally generated constraint triggers 541 requires superuser privileges; it should be done with caution since 542 of course the integrity of the constraint cannot be guaranteed if the 543 triggers are not executed. 544 </para> 545 546 <para> 547 The trigger firing mechanism is also affected by the configuration 548 variable <xref linkend="guc-session-replication-role"/>. Simply enabled 549 triggers (the default) will fire when the replication role is <quote>origin</quote> 550 (the default) or <quote>local</quote>. Triggers configured as <literal>ENABLE 551 REPLICA</literal> will only fire if the session is in <quote>replica</quote> 552 mode, and triggers configured as <literal>ENABLE ALWAYS</literal> will 553 fire regardless of the current replication role. 554 </para> 555 556 <para> 557 The effect of this mechanism is that in the default configuration, 558 triggers do not fire on replicas. This is useful because if a trigger 559 is used on the origin to propagate data between tables, then the 560 replication system will also replicate the propagated data, and the 561 trigger should not fire a second time on the replica, because that would 562 lead to duplication. However, if a trigger is used for another purpose 563 such as creating external alerts, then it might be appropriate to set it 564 to <literal>ENABLE ALWAYS</literal> so that it is also fired on 565 replicas. 566 </para> 567 568 <para> 569 This command acquires a <literal>SHARE ROW EXCLUSIVE</literal> lock. 570 </para> 571 </listitem> 572 </varlistentry> 573 574 <varlistentry> 575 <term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] RULE</literal></term> 576 <listitem> 577 <para> 578 These forms configure the firing of rewrite rules belonging to the table. 579 A disabled rule is still known to the system, but is not applied 580 during query rewriting. The semantics are as for disabled/enabled 581 triggers. This configuration is ignored for <literal>ON SELECT</literal> rules, which 582 are always applied in order to keep views working even if the current 583 session is in a non-default replication role. 584 </para> 585 586 <para> 587 The rule firing mechanism is also affected by the configuration variable 588 <xref linkend="guc-session-replication-role"/>, analogous to triggers as 589 described above. 590 </para> 591 </listitem> 592 </varlistentry> 593 594 <varlistentry> 595 <term><literal>DISABLE</literal>/<literal>ENABLE ROW LEVEL SECURITY</literal></term> 596 <listitem> 597 <para> 598 These forms control the application of row security policies belonging 599 to the table. If enabled and no policies exist for the table, then a 600 default-deny policy is applied. Note that policies can exist for a table 601 even if row level security is disabled. In this case, the policies will 602 <emphasis>not</emphasis> be applied and the policies will be ignored. 603 See also 604 <xref linkend="sql-createpolicy"/>. 605 </para> 606 </listitem> 607 </varlistentry> 608 609 <varlistentry> 610 <term><literal>NO FORCE</literal>/<literal>FORCE ROW LEVEL SECURITY</literal></term> 611 <listitem> 612 <para> 613 These forms control the application of row security policies belonging 614 to the table when the user is the table owner. If enabled, row level 615 security policies will be applied when the user is the table owner. If 616 disabled (the default) then row level security will not be applied when 617 the user is the table owner. 618 See also 619 <xref linkend="sql-createpolicy"/>. 620 </para> 621 </listitem> 622 </varlistentry> 623 624 <varlistentry> 625 <term><literal>CLUSTER ON</literal></term> 626 <listitem> 627 <para> 628 This form selects the default index for future 629 <xref linkend="sql-cluster"/> 630 operations. It does not actually re-cluster the table. 631 </para> 632 <para> 633 Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock. 634 </para> 635 </listitem> 636 </varlistentry> 637 638 <varlistentry> 639 <term><literal>SET WITHOUT CLUSTER</literal></term> 640 <listitem> 641 <para> 642 This form removes the most recently used 643 <xref linkend="sql-cluster"/> 644 index specification from the table. This affects 645 future cluster operations that don't specify an index. 646 </para> 647 <para> 648 Changing cluster options acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock. 649 </para> 650 </listitem> 651 </varlistentry> 652 653 <varlistentry> 654 <term><literal>SET WITHOUT OIDS</literal></term> 655 <listitem> 656 <para> 657 Backward-compatible syntax for removing the <literal>oid</literal> 658 system column. As <literal>oid</literal> system columns cannot be 659 added anymore, this never has an effect. 660 </para> 661 </listitem> 662 </varlistentry> 663 664 <varlistentry> 665 <term><literal>SET TABLESPACE</literal></term> 666 <listitem> 667 <para> 668 This form changes the table's tablespace to the specified tablespace and 669 moves the data file(s) associated with the table to the new tablespace. 670 Indexes on the table, if any, are not moved; but they can be moved 671 separately with additional <literal>SET TABLESPACE</literal> commands. 672 When applied to a partitioned table, nothing is moved, but any 673 partitions created afterwards with 674 <command>CREATE TABLE PARTITION OF</command> will use that tablespace, 675 unless overridden by a <literal>TABLESPACE</literal> clause. 676 </para> 677 678 <para> 679 All tables in the current database in a tablespace can be moved by using 680 the <literal>ALL IN TABLESPACE</literal> form, which will lock all tables 681 to be moved first and then move each one. This form also supports 682 <literal>OWNED BY</literal>, which will only move tables owned by the 683 roles specified. If the <literal>NOWAIT</literal> option is specified 684 then the command will fail if it is unable to acquire all of the locks 685 required immediately. Note that system catalogs are not moved by this 686 command; use <command>ALTER DATABASE</command> or explicit 687 <command>ALTER TABLE</command> invocations instead if desired. The 688 <literal>information_schema</literal> relations are not considered part 689 of the system catalogs and will be moved. 690 See also 691 <xref linkend="sql-createtablespace"/>. 692 </para> 693 </listitem> 694 </varlistentry> 695 696 <varlistentry> 697 <term><literal>SET { LOGGED | UNLOGGED }</literal></term> 698 <listitem> 699 <para> 700 This form changes the table from unlogged to logged or vice-versa 701 (see <xref linkend="sql-createtable-unlogged"/>). It cannot be applied 702 to a temporary table. 703 </para> 704 </listitem> 705 </varlistentry> 706 707 <varlistentry> 708 <term><literal>SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term> 709 <listitem> 710 <para> 711 This form changes one or more storage parameters for the table. See 712 <xref linkend="sql-createtable-storage-parameters"/> in the 713 <xref linkend="sql-createtable"/> documentation 714 for details on the available parameters. Note that the table contents 715 will not be modified immediately by this command; depending on the 716 parameter you might need to rewrite the table to get the desired effects. 717 That can be done with <link linkend="sql-vacuum">VACUUM 718 FULL</link>, <xref linkend="sql-cluster"/> or one of the forms 719 of <command>ALTER TABLE</command> that forces a table rewrite. 720 For planner related parameters, changes will take effect from the next 721 time the table is locked so currently executing queries will not be 722 affected. 723 </para> 724 725 <para> 726 <literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for 727 fillfactor, toast and autovacuum storage parameters, as well as the 728 planner parameter <varname>parallel_workers</varname>. 729 </para> 730 </listitem> 731 </varlistentry> 732 733 <varlistentry> 734 <term><literal>RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] )</literal></term> 735 <listitem> 736 <para> 737 This form resets one or more storage parameters to their 738 defaults. As with <literal>SET</literal>, a table rewrite might be 739 needed to update the table entirely. 740 </para> 741 </listitem> 742 </varlistentry> 743 744 <varlistentry> 745 <term><literal>INHERIT <replaceable class="parameter">parent_table</replaceable></literal></term> 746 <listitem> 747 <para> 748 This form adds the target table as a new child of the specified parent 749 table. Subsequently, queries against the parent will include records 750 of the target table. To be added as a child, the target table must 751 already contain all the same columns as the parent (it could have 752 additional columns, too). The columns must have matching data types, 753 and if they have <literal>NOT NULL</literal> constraints in the parent 754 then they must also have <literal>NOT NULL</literal> constraints in the 755 child. 756 </para> 757 758 <para> 759 There must also be matching child-table constraints for all 760 <literal>CHECK</literal> constraints of the parent, except those 761 marked non-inheritable (that is, created with <literal>ALTER TABLE ... ADD CONSTRAINT ... NO INHERIT</literal>) 762 in the parent, which are ignored; all child-table constraints matched 763 must not be marked non-inheritable. 764 Currently 765 <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and 766 <literal>FOREIGN KEY</literal> constraints are not considered, but 767 this might change in the future. 768 </para> 769 </listitem> 770 </varlistentry> 771 772 <varlistentry> 773 <term><literal>NO INHERIT <replaceable class="parameter">parent_table</replaceable></literal></term> 774 <listitem> 775 <para> 776 This form removes the target table from the list of children of the 777 specified parent table. 778 Queries against the parent table will no longer include records drawn 779 from the target table. 780 </para> 781 </listitem> 782 </varlistentry> 783 784 <varlistentry> 785 <term><literal>OF <replaceable class="parameter">type_name</replaceable></literal></term> 786 <listitem> 787 <para> 788 This form links the table to a composite type as though <command>CREATE 789 TABLE OF</command> had formed it. The table's list of column names and types 790 must precisely match that of the composite type. The table must 791 not inherit from any other table. These restrictions ensure 792 that <command>CREATE TABLE OF</command> would permit an equivalent table 793 definition. 794 </para> 795 </listitem> 796 </varlistentry> 797 798 <varlistentry> 799 <term><literal>NOT OF</literal></term> 800 <listitem> 801 <para> 802 This form dissociates a typed table from its type. 803 </para> 804 </listitem> 805 </varlistentry> 806 807 <varlistentry> 808 <term><literal>OWNER TO</literal></term> 809 <listitem> 810 <para> 811 This form changes the owner of the table, sequence, view, materialized view, 812 or foreign table to the specified user. 813 </para> 814 </listitem> 815 </varlistentry> 816 817 <varlistentry id="sql-createtable-replica-identity"> 818 <term><literal>REPLICA IDENTITY</literal></term> 819 <listitem> 820 <para> 821 This form changes the information which is written to the write-ahead log 822 to identify rows which are updated or deleted. This option has no effect 823 except when logical replication is in use. <literal>DEFAULT</literal> 824 (the default for non-system tables) records the 825 old values of the columns of the primary key, if any. <literal>USING INDEX</literal> 826 records the old values of the columns covered by the named index, which 827 must be unique, not partial, not deferrable, and include only columns marked 828 <literal>NOT NULL</literal>. <literal>FULL</literal> records the old values of all columns 829 in the row. <literal>NOTHING</literal> records no information about the old row. 830 (This is the default for system tables.) 831 In all cases, no old values are logged unless at least one of the columns 832 that would be logged differs between the old and new versions of the row. 833 </para> 834 </listitem> 835 </varlistentry> 836 837 <varlistentry> 838 <term><literal>RENAME</literal></term> 839 <listitem> 840 <para> 841 The <literal>RENAME</literal> forms change the name of a table 842 (or an index, sequence, view, materialized view, or foreign table), the 843 name of an individual column in a table, or the name of a constraint of 844 the table. When renaming a constraint that has an underlying index, 845 the index is renamed as well. 846 There is no effect on the stored data. 847 </para> 848 </listitem> 849 </varlistentry> 850 851 <varlistentry> 852 <term><literal>SET SCHEMA</literal></term> 853 <listitem> 854 <para> 855 This form moves the table into another schema. Associated indexes, 856 constraints, and sequences owned by table columns are moved as well. 857 </para> 858 </listitem> 859 </varlistentry> 860 861 <varlistentry id="sql-altertable-attach-partition"> 862 <term><literal>ATTACH PARTITION <replaceable class="parameter">partition_name</replaceable> { FOR VALUES <replaceable class="parameter">partition_bound_spec</replaceable> | DEFAULT }</literal></term> 863 <listitem> 864 <para> 865 This form attaches an existing table (which might itself be partitioned) 866 as a partition of the target table. The table can be attached 867 as a partition for specific values using <literal>FOR VALUES</literal> 868 or as a default partition by using <literal>DEFAULT</literal>. 869 For each index in the target table, a corresponding 870 one will be created in the attached table; or, if an equivalent 871 index already exists, it will be attached to the target table's index, 872 as if <command>ALTER INDEX ATTACH PARTITION</command> had been executed. 873 Note that if the existing table is a foreign table, it is currently not 874 allowed to attach the table as a partition of the target table if there 875 are <literal>UNIQUE</literal> indexes on the target table. (See also 876 <xref linkend="sql-createforeigntable"/>.) For each user-defined 877 row-level trigger that exists in the target table, a corresponding one 878 is created in the attached table. 879 </para> 880 881 <para> 882 A partition using <literal>FOR VALUES</literal> uses same syntax for 883 <replaceable class="parameter">partition_bound_spec</replaceable> as 884 <xref linkend="sql-createtable"/>. The partition bound specification 885 must correspond to the partitioning strategy and partition key of the 886 target table. The table to be attached must have all the same columns 887 as the target table and no more; moreover, the column types must also 888 match. Also, it must have all the <literal>NOT NULL</literal> and 889 <literal>CHECK</literal> constraints of the target table. Currently 890 <literal>FOREIGN KEY</literal> constraints are not considered. 891 <literal>UNIQUE</literal> and <literal>PRIMARY KEY</literal> constraints 892 from the parent table will be created in the partition, if they don't 893 already exist. 894 If any of the <literal>CHECK</literal> constraints of the table being 895 attached are marked <literal>NO INHERIT</literal>, the command will fail; 896 such constraints must be recreated without the 897 <literal>NO INHERIT</literal> clause. 898 </para> 899 900 <para> 901 If the new partition is a regular table, a full table scan is performed 902 to check that existing rows in the table do not violate the partition 903 constraint. It is possible to avoid this scan by adding a valid 904 <literal>CHECK</literal> constraint to the table that allows only 905 rows satisfying the desired partition constraint before running this 906 command. The <literal>CHECK</literal> constraint will be used to 907 determine that the table need not be scanned to validate the partition 908 constraint. This does not work, however, if any of the partition keys 909 is an expression and the partition does not accept 910 <literal>NULL</literal> values. If attaching a list partition that will 911 not accept <literal>NULL</literal> values, also add 912 <literal>NOT NULL</literal> constraint to the partition key column, 913 unless it's an expression. 914 </para> 915 916 <para> 917 If the new partition is a foreign table, nothing is done to verify 918 that all the rows in the foreign table obey the partition constraint. 919 (See the discussion in <xref linkend="sql-createforeigntable"/> about 920 constraints on the foreign table.) 921 </para> 922 923 <para> 924 When a table has a default partition, defining a new partition changes 925 the partition constraint for the default partition. The default 926 partition can't contain any rows that would need to be moved to the new 927 partition, and will be scanned to verify that none are present. This 928 scan, like the scan of the new partition, can be avoided if an 929 appropriate <literal>CHECK</literal> constraint is present. Also like 930 the scan of the new partition, it is always skipped when the default 931 partition is a foreign table. 932 </para> 933 934 <para> 935 Attaching a partition acquires a 936 <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the parent table, 937 in addition to the <literal>ACCESS EXCLUSIVE</literal> locks on the table 938 being attached and on the default partition (if any). 939 </para> 940 941 <para> 942 Further locks must also be held on all sub-partitions if the table being 943 attached is itself a partitioned table. Likewise if the default 944 partition is itself a partitioned table. The locking of the 945 sub-partitions can be avoided by adding a <literal>CHECK</literal> 946 constraint as described in 947 <xref linkend="ddl-partitioning-declarative-maintenance"/>. 948 </para> 949 </listitem> 950 </varlistentry> 951 952 <varlistentry> 953 <term><literal>DETACH PARTITION</literal> <replaceable class="parameter">partition_name</replaceable></term> 954 <listitem> 955 <para> 956 This form detaches the specified partition of the target table. The detached 957 partition continues to exist as a standalone table, but no longer has any 958 ties to the table from which it was detached. Any indexes that were 959 attached to the target table's indexes are detached. Any triggers that 960 were created as clones of those in the target table are removed. 961 <literal>SHARE</literal> lock is obtained on any tables that reference 962 this partitioned table in foreign key constraints. 963 </para> 964 </listitem> 965 </varlistentry> 966 967 </variablelist> 968 </para> 969 970 <para> 971 All the forms of ALTER TABLE that act on a single table, except 972 <literal>RENAME</literal>, <literal>SET SCHEMA</literal>, 973 <literal>ATTACH PARTITION</literal>, and 974 <literal>DETACH PARTITION</literal> can be combined into 975 a list of multiple alterations to be applied together. For example, it 976 is possible to add several columns and/or alter the type of several 977 columns in a single command. This is particularly useful with large 978 tables, since only one pass over the table need be made. 979 </para> 980 981 <para> 982 You must own the table to use <command>ALTER TABLE</command>. 983 To change the schema or tablespace of a table, you must also have 984 <literal>CREATE</literal> privilege on the new schema or tablespace. 985 To add the table as a new child of a parent table, you must own the parent 986 table as well. Also, to attach a table as a new partition of the table, 987 you must own the table being attached. 988 To alter the owner, you must also be a direct or indirect member of the new 989 owning role, and that role must have <literal>CREATE</literal> privilege on 990 the table's schema. (These restrictions enforce that altering the owner 991 doesn't do anything you couldn't do by dropping and recreating the table. 992 However, a superuser can alter ownership of any table anyway.) 993 To add a column or alter a column type or use the <literal>OF</literal> 994 clause, you must also have <literal>USAGE</literal> privilege on the data 995 type. 996 </para> 997 </refsect1> 998 999 <refsect1> 1000 <title>Parameters</title> 1001 1002 <variablelist> 1003 1004 <varlistentry> 1005 <term><literal>IF EXISTS</literal></term> 1006 <listitem> 1007 <para> 1008 Do not throw an error if the table does not exist. A notice is issued 1009 in this case. 1010 </para> 1011 </listitem> 1012 </varlistentry> 1013 1014 <varlistentry> 1015 <term><replaceable class="parameter">name</replaceable></term> 1016 <listitem> 1017 <para> 1018 The name (optionally schema-qualified) of an existing table to 1019 alter. If <literal>ONLY</literal> is specified before the table name, only 1020 that table is altered. If <literal>ONLY</literal> is not specified, the table 1021 and all its descendant tables (if any) are altered. Optionally, 1022 <literal>*</literal> can be specified after the table name to explicitly 1023 indicate that descendant tables are included. 1024 </para> 1025 </listitem> 1026 </varlistentry> 1027 1028 <varlistentry> 1029 <term><replaceable class="parameter">column_name</replaceable></term> 1030 <listitem> 1031 <para> 1032 Name of a new or existing column. 1033 </para> 1034 </listitem> 1035 </varlistentry> 1036 1037 <varlistentry> 1038 <term><replaceable class="parameter">new_column_name</replaceable></term> 1039 <listitem> 1040 <para> 1041 New name for an existing column. 1042 </para> 1043 </listitem> 1044 </varlistentry> 1045 1046 <varlistentry> 1047 <term><replaceable class="parameter">new_name</replaceable></term> 1048 <listitem> 1049 <para> 1050 New name for the table. 1051 </para> 1052 </listitem> 1053 </varlistentry> 1054 1055 <varlistentry> 1056 <term><replaceable class="parameter">data_type</replaceable></term> 1057 <listitem> 1058 <para> 1059 Data type of the new column, or new data type for an existing 1060 column. 1061 </para> 1062 </listitem> 1063 </varlistentry> 1064 1065 <varlistentry> 1066 <term><replaceable class="parameter">table_constraint</replaceable></term> 1067 <listitem> 1068 <para> 1069 New table constraint for the table. 1070 </para> 1071 </listitem> 1072 </varlistentry> 1073 1074 <varlistentry> 1075 <term><replaceable class="parameter">constraint_name</replaceable></term> 1076 <listitem> 1077 <para> 1078 Name of a new or existing constraint. 1079 </para> 1080 </listitem> 1081 </varlistentry> 1082 1083 <varlistentry> 1084 <term><literal>CASCADE</literal></term> 1085 <listitem> 1086 <para> 1087 Automatically drop objects that depend on the dropped column 1088 or constraint (for example, views referencing the column), 1089 and in turn all objects that depend on those objects 1090 (see <xref linkend="ddl-depend"/>). 1091 </para> 1092 </listitem> 1093 </varlistentry> 1094 1095 <varlistentry> 1096 <term><literal>RESTRICT</literal></term> 1097 <listitem> 1098 <para> 1099 Refuse to drop the column or constraint if there are any dependent 1100 objects. This is the default behavior. 1101 </para> 1102 </listitem> 1103 </varlistentry> 1104 1105 <varlistentry> 1106 <term><replaceable class="parameter">trigger_name</replaceable></term> 1107 <listitem> 1108 <para> 1109 Name of a single trigger to disable or enable. 1110 </para> 1111 </listitem> 1112 </varlistentry> 1113 1114 <varlistentry> 1115 <term><literal>ALL</literal></term> 1116 <listitem> 1117 <para> 1118 Disable or enable all triggers belonging to the table. 1119 (This requires superuser privilege if any of the triggers are 1120 internally generated constraint triggers such as those that are used 1121 to implement foreign key constraints or deferrable uniqueness and 1122 exclusion constraints.) 1123 </para> 1124 </listitem> 1125 </varlistentry> 1126 1127 <varlistentry> 1128 <term><literal>USER</literal></term> 1129 <listitem> 1130 <para> 1131 Disable or enable all triggers belonging to the table except for 1132 internally generated constraint triggers such as those that are used 1133 to implement foreign key constraints or deferrable uniqueness and 1134 exclusion constraints. 1135 </para> 1136 </listitem> 1137 </varlistentry> 1138 1139 <varlistentry> 1140 <term><replaceable class="parameter">index_name</replaceable></term> 1141 <listitem> 1142 <para> 1143 The name of an existing index. 1144 </para> 1145 </listitem> 1146 </varlistentry> 1147 1148 <varlistentry> 1149 <term><replaceable class="parameter">storage_parameter</replaceable></term> 1150 <listitem> 1151 <para> 1152 The name of a table storage parameter. 1153 </para> 1154 </listitem> 1155 </varlistentry> 1156 1157 <varlistentry> 1158 <term><replaceable class="parameter">value</replaceable></term> 1159 <listitem> 1160 <para> 1161 The new value for a table storage parameter. 1162 This might be a number or a word depending on the parameter. 1163 </para> 1164 </listitem> 1165 </varlistentry> 1166 1167 <varlistentry> 1168 <term><replaceable class="parameter">parent_table</replaceable></term> 1169 <listitem> 1170 <para> 1171 A parent table to associate or de-associate with this table. 1172 </para> 1173 </listitem> 1174 </varlistentry> 1175 1176 <varlistentry> 1177 <term><replaceable class="parameter">new_owner</replaceable></term> 1178 <listitem> 1179 <para> 1180 The user name of the new owner of the table. 1181 </para> 1182 </listitem> 1183 </varlistentry> 1184 1185 <varlistentry> 1186 <term><replaceable class="parameter">new_tablespace</replaceable></term> 1187 <listitem> 1188 <para> 1189 The name of the tablespace to which the table will be moved. 1190 </para> 1191 </listitem> 1192 </varlistentry> 1193 1194 <varlistentry> 1195 <term><replaceable class="parameter">new_schema</replaceable></term> 1196 <listitem> 1197 <para> 1198 The name of the schema to which the table will be moved. 1199 </para> 1200 </listitem> 1201 </varlistentry> 1202 1203 <varlistentry> 1204 <term><replaceable class="parameter">partition_name</replaceable></term> 1205 <listitem> 1206 <para> 1207 The name of the table to attach as a new partition or to detach from this table. 1208 </para> 1209 </listitem> 1210 </varlistentry> 1211 1212 <varlistentry> 1213 <term><replaceable class="parameter">partition_bound_spec</replaceable></term> 1214 <listitem> 1215 <para> 1216 The partition bound specification for a new partition. Refer to 1217 <xref linkend="sql-createtable"/> for more details on the syntax of the same. 1218 </para> 1219 </listitem> 1220 </varlistentry> 1221 1222 </variablelist> 1223 </refsect1> 1224 1225 <refsect1 id="sql-altertable-notes" xreflabel="Notes"> 1226 <title>Notes</title> 1227 1228 <para> 1229 The key word <literal>COLUMN</literal> is noise and can be omitted. 1230 </para> 1231 1232 <para> 1233 When a column is added with <literal>ADD COLUMN</literal> and a 1234 non-volatile <literal>DEFAULT</literal> is specified, the default is 1235 evaluated at the time of the statement and the result stored in the 1236 table's metadata. That value will be used for the column for all existing 1237 rows. If no <literal>DEFAULT</literal> is specified, NULL is used. In 1238 neither case is a rewrite of the table required. 1239 </para> 1240 1241 <para> 1242 Adding a column with a volatile <literal>DEFAULT</literal> or 1243 changing the type of an existing column will require the entire table and 1244 its indexes to be rewritten. As an exception, when changing the type of an 1245 existing column, if the <literal>USING</literal> clause does not change 1246 the column contents and the old type is either binary coercible to the new 1247 type or an unconstrained domain over the new type, a table rewrite is not 1248 needed; but any indexes on the affected columns must still be rebuilt. 1249 Table and/or index rebuilds may take a 1250 significant amount of time for a large table; and will temporarily require 1251 as much as double the disk space. 1252 </para> 1253 1254 <para> 1255 Adding a <literal>CHECK</literal> or <literal>NOT NULL</literal> constraint requires 1256 scanning the table to verify that existing rows meet the constraint, 1257 but does not require a table rewrite. 1258 </para> 1259 1260 <para> 1261 Similarly, when attaching a new partition it may be scanned to verify that 1262 existing rows meet the partition constraint. 1263 </para> 1264 1265 <para> 1266 The main reason for providing the option to specify multiple changes 1267 in a single <command>ALTER TABLE</command> is that multiple table scans or 1268 rewrites can thereby be combined into a single pass over the table. 1269 </para> 1270 1271 <para> 1272 Scanning a large table to verify a new foreign key or check constraint 1273 can take a long time, and other updates to the table are locked out 1274 until the <command>ALTER TABLE ADD CONSTRAINT</command> command is 1275 committed. The main purpose of the <literal>NOT VALID</literal> 1276 constraint option is to reduce the impact of adding a constraint on 1277 concurrent updates. With <literal>NOT VALID</literal>, 1278 the <command>ADD CONSTRAINT</command> command does not scan the table 1279 and can be committed immediately. After that, a <literal>VALIDATE 1280 CONSTRAINT</literal> command can be issued to verify that existing rows 1281 satisfy the constraint. The validation step does not need to lock out 1282 concurrent updates, since it knows that other transactions will be 1283 enforcing the constraint for rows that they insert or update; only 1284 pre-existing rows need to be checked. Hence, validation acquires only 1285 a <literal>SHARE UPDATE EXCLUSIVE</literal> lock on the table being 1286 altered. (If the constraint is a foreign key then a <literal>ROW 1287 SHARE</literal> lock is also required on the table referenced by the 1288 constraint.) In addition to improving concurrency, it can be useful to 1289 use <literal>NOT VALID</literal> and <literal>VALIDATE 1290 CONSTRAINT</literal> in cases where the table is known to contain 1291 pre-existing violations. Once the constraint is in place, no new 1292 violations can be inserted, and the existing problems can be corrected 1293 at leisure until <literal>VALIDATE CONSTRAINT</literal> finally 1294 succeeds. 1295 </para> 1296 1297 <para> 1298 The <literal>DROP COLUMN</literal> form does not physically remove 1299 the column, but simply makes it invisible to SQL operations. Subsequent 1300 insert and update operations in the table will store a null value for the 1301 column. Thus, dropping a column is quick but it will not immediately 1302 reduce the on-disk size of your table, as the space occupied 1303 by the dropped column is not reclaimed. The space will be 1304 reclaimed over time as existing rows are updated. 1305 </para> 1306 1307 <para> 1308 To force immediate reclamation of space occupied by a dropped column, 1309 you can execute one of the forms of <command>ALTER TABLE</command> that 1310 performs a rewrite of the whole table. This results in reconstructing 1311 each row with the dropped column replaced by a null value. 1312 </para> 1313 1314 <para> 1315 The rewriting forms of <command>ALTER TABLE</command> are not MVCC-safe. 1316 After a table rewrite, the table will appear empty to concurrent 1317 transactions, if they are using a snapshot taken before the rewrite 1318 occurred. See <xref linkend="mvcc-caveats"/> for more details. 1319 </para> 1320 1321 <para> 1322 The <literal>USING</literal> option of <literal>SET DATA TYPE</literal> can actually 1323 specify any expression involving the old values of the row; that is, it 1324 can refer to other columns as well as the one being converted. This allows 1325 very general conversions to be done with the <literal>SET DATA TYPE</literal> 1326 syntax. Because of this flexibility, the <literal>USING</literal> 1327 expression is not applied to the column's default value (if any); the 1328 result might not be a constant expression as required for a default. 1329 This means that when there is no implicit or assignment cast from old to 1330 new type, <literal>SET DATA TYPE</literal> might fail to convert the default even 1331 though a <literal>USING</literal> clause is supplied. In such cases, 1332 drop the default with <literal>DROP DEFAULT</literal>, perform the <literal>ALTER 1333 TYPE</literal>, and then use <literal>SET DEFAULT</literal> to add a suitable new 1334 default. Similar considerations apply to indexes and constraints involving 1335 the column. 1336 </para> 1337 1338 <para> 1339 If a table has any descendant tables, it is not permitted to add, 1340 rename, or change the type of a column in the parent table without doing 1341 the same to the descendants. This ensures that the descendants always 1342 have columns matching the parent. Similarly, a <literal>CHECK</literal> 1343 constraint cannot be renamed in the parent without also renaming it in 1344 all descendants, so that <literal>CHECK</literal> constraints also match 1345 between the parent and its descendants. (That restriction does not apply 1346 to index-based constraints, however.) 1347 Also, because selecting from the parent also selects from its descendants, 1348 a constraint on the parent cannot be marked valid unless it is also marked 1349 valid for those descendants. In all of these cases, <command>ALTER TABLE 1350 ONLY</command> will be rejected. 1351 </para> 1352 1353 <para> 1354 A recursive <literal>DROP COLUMN</literal> operation will remove a 1355 descendant table's column only if the descendant does not inherit 1356 that column from any other parents and never had an independent 1357 definition of the column. A nonrecursive <literal>DROP 1358 COLUMN</literal> (i.e., <command>ALTER TABLE ONLY ... DROP 1359 COLUMN</command>) never removes any descendant columns, but 1360 instead marks them as independently defined rather than inherited. 1361 A nonrecursive <literal>DROP COLUMN</literal> command will fail for a 1362 partitioned table, because all partitions of a table must have the same 1363 columns as the partitioning root. 1364 </para> 1365 1366 <para> 1367 The actions for identity columns (<literal>ADD 1368 GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP 1369 IDENTITY</literal>), as well as the actions 1370 <literal>TRIGGER</literal>, <literal>CLUSTER</literal>, <literal>OWNER</literal>, 1371 and <literal>TABLESPACE</literal> never recurse to descendant tables; 1372 that is, they always act as though <literal>ONLY</literal> were specified. 1373 Adding a constraint recurses only for <literal>CHECK</literal> constraints 1374 that are not marked <literal>NO INHERIT</literal>. 1375 </para> 1376 1377 <para> 1378 Changing any part of a system catalog table is not permitted. 1379 </para> 1380 1381 <para> 1382 Refer to <xref linkend="sql-createtable"/> for a further description of valid 1383 parameters. <xref linkend="ddl"/> has further information on 1384 inheritance. 1385 </para> 1386 </refsect1> 1387 1388 <refsect1> 1389 <title>Examples</title> 1390 1391 <para> 1392 To add a column of type <type>varchar</type> to a table: 1393<programlisting> 1394ALTER TABLE distributors ADD COLUMN address varchar(30); 1395</programlisting> 1396 That will cause all existing rows in the table to be filled with null 1397 values for the new column. 1398 </para> 1399 1400 <para> 1401 To add a column with a non-null default: 1402<programlisting> 1403ALTER TABLE measurements 1404 ADD COLUMN mtime timestamp with time zone DEFAULT now(); 1405</programlisting> 1406 Existing rows will be filled with the current time as the value of the 1407 new column, and then new rows will receive the time of their insertion. 1408 </para> 1409 1410 <para> 1411 To add a column and fill it with a value different from the default to 1412 be used later: 1413<programlisting> 1414ALTER TABLE transactions 1415 ADD COLUMN status varchar(30) DEFAULT 'old', 1416 ALTER COLUMN status SET default 'current'; 1417</programlisting> 1418 Existing rows will be filled with <literal>old</literal>, but then 1419 the default for subsequent commands will be <literal>current</literal>. 1420 The effects are the same as if the two sub-commands had been issued 1421 in separate <command>ALTER TABLE</command> commands. 1422 </para> 1423 1424 <para> 1425 To drop a column from a table: 1426<programlisting> 1427ALTER TABLE distributors DROP COLUMN address RESTRICT; 1428</programlisting> 1429 </para> 1430 1431 <para> 1432 To change the types of two existing columns in one operation: 1433<programlisting> 1434ALTER TABLE distributors 1435 ALTER COLUMN address TYPE varchar(80), 1436 ALTER COLUMN name TYPE varchar(100); 1437</programlisting> 1438 </para> 1439 1440 <para> 1441 To change an integer column containing Unix timestamps to <type>timestamp 1442 with time zone</type> via a <literal>USING</literal> clause: 1443<programlisting> 1444ALTER TABLE foo 1445 ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone 1446 USING 1447 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second'; 1448</programlisting> 1449 </para> 1450 1451 <para> 1452 The same, when the column has a default expression that won't automatically 1453 cast to the new data type: 1454<programlisting> 1455ALTER TABLE foo 1456 ALTER COLUMN foo_timestamp DROP DEFAULT, 1457 ALTER COLUMN foo_timestamp TYPE timestamp with time zone 1458 USING 1459 timestamp with time zone 'epoch' + foo_timestamp * interval '1 second', 1460 ALTER COLUMN foo_timestamp SET DEFAULT now(); 1461</programlisting> 1462 </para> 1463 1464 <para> 1465 To rename an existing column: 1466<programlisting> 1467ALTER TABLE distributors RENAME COLUMN address TO city; 1468</programlisting> 1469 </para> 1470 1471 <para> 1472 To rename an existing table: 1473<programlisting> 1474ALTER TABLE distributors RENAME TO suppliers; 1475</programlisting> 1476 </para> 1477 1478 <para> 1479 To rename an existing constraint: 1480<programlisting> 1481ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check; 1482</programlisting> 1483 </para> 1484 1485 <para> 1486 To add a not-null constraint to a column: 1487<programlisting> 1488ALTER TABLE distributors ALTER COLUMN street SET NOT NULL; 1489</programlisting> 1490 To remove a not-null constraint from a column: 1491<programlisting> 1492ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL; 1493</programlisting> 1494 </para> 1495 1496 <para> 1497 To add a check constraint to a table and all its children: 1498<programlisting> 1499ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5); 1500</programlisting> 1501 </para> 1502 1503 <para> 1504 To add a check constraint only to a table and not to its children: 1505<programlisting> 1506ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT; 1507</programlisting> 1508 (The check constraint will not be inherited by future children, either.) 1509 </para> 1510 1511 <para> 1512 To remove a check constraint from a table and all its children: 1513<programlisting> 1514ALTER TABLE distributors DROP CONSTRAINT zipchk; 1515</programlisting> 1516 </para> 1517 1518 <para> 1519 To remove a check constraint from one table only: 1520<programlisting> 1521ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk; 1522</programlisting> 1523 (The check constraint remains in place for any child tables.) 1524 </para> 1525 1526 <para> 1527 To add a foreign key constraint to a table: 1528<programlisting> 1529ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address); 1530</programlisting> 1531 </para> 1532 1533 <para> 1534 To add a foreign key constraint to a table with the least impact on other work: 1535<programlisting> 1536ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID; 1537ALTER TABLE distributors VALIDATE CONSTRAINT distfk; 1538</programlisting> 1539 </para> 1540 1541 <para> 1542 To add a (multicolumn) unique constraint to a table: 1543<programlisting> 1544ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode); 1545</programlisting> 1546 </para> 1547 1548 <para> 1549 To add an automatically named primary key constraint to a table, noting 1550 that a table can only ever have one primary key: 1551<programlisting> 1552ALTER TABLE distributors ADD PRIMARY KEY (dist_id); 1553</programlisting> 1554 </para> 1555 1556 <para> 1557 To move a table to a different tablespace: 1558<programlisting> 1559ALTER TABLE distributors SET TABLESPACE fasttablespace; 1560</programlisting> 1561 </para> 1562 1563 <para> 1564 To move a table to a different schema: 1565<programlisting> 1566ALTER TABLE myschema.distributors SET SCHEMA yourschema; 1567</programlisting> 1568 </para> 1569 1570 <para> 1571 To recreate a primary key constraint, without blocking updates while the 1572 index is rebuilt: 1573<programlisting> 1574CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id); 1575ALTER TABLE distributors DROP CONSTRAINT distributors_pkey, 1576 ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx; 1577</programlisting></para> 1578 1579 <para> 1580 To attach a partition to a range-partitioned table: 1581<programlisting> 1582ALTER TABLE measurement 1583 ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); 1584</programlisting></para> 1585 1586 <para> 1587 To attach a partition to a list-partitioned table: 1588<programlisting> 1589ALTER TABLE cities 1590 ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b'); 1591</programlisting></para> 1592 1593 <para> 1594 To attach a partition to a hash-partitioned table: 1595<programlisting> 1596ALTER TABLE orders 1597 ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3); 1598</programlisting></para> 1599 1600 <para> 1601 To attach a default partition to a partitioned table: 1602<programlisting> 1603ALTER TABLE cities 1604 ATTACH PARTITION cities_partdef DEFAULT; 1605</programlisting></para> 1606 1607 <para> 1608 To detach a partition from a partitioned table: 1609<programlisting> 1610ALTER TABLE measurement 1611 DETACH PARTITION measurement_y2015m12; 1612</programlisting></para> 1613 1614 </refsect1> 1615 1616 <refsect1> 1617 <title>Compatibility</title> 1618 1619 <para> 1620 The forms <literal>ADD</literal> (without <literal>USING INDEX</literal>), 1621 <literal>DROP [COLUMN]</literal>, <literal>DROP IDENTITY</literal>, <literal>RESTART</literal>, 1622 <literal>SET DEFAULT</literal>, <literal>SET DATA TYPE</literal> (without <literal>USING</literal>), 1623 <literal>SET GENERATED</literal>, and <literal>SET <replaceable>sequence_option</replaceable></literal> 1624 conform with the SQL standard. The other forms are 1625 <productname>PostgreSQL</productname> extensions of the SQL standard. 1626 Also, the ability to specify more than one manipulation in a single 1627 <command>ALTER TABLE</command> command is an extension. 1628 </para> 1629 1630 <para> 1631 <command>ALTER TABLE DROP COLUMN</command> can be used to drop the only 1632 column of a table, leaving a zero-column table. This is an 1633 extension of SQL, which disallows zero-column tables. 1634 </para> 1635 </refsect1> 1636 1637 <refsect1> 1638 <title>See Also</title> 1639 1640 <simplelist type="inline"> 1641 <member><xref linkend="sql-createtable"/></member> 1642 </simplelist> 1643 </refsect1> 1644</refentry> 1645