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