1<!-- 2doc/src/sgml/rel/alter_foreign_table.sgml 3PostgreSQL documentation 4--> 5 6<refentry id="SQL-ALTERFOREIGNTABLE"> 7 <indexterm zone="sql-alterforeigntable"> 8 <primary>ALTER FOREIGN TABLE</primary> 9 </indexterm> 10 11 <refmeta> 12 <refentrytitle>ALTER FOREIGN TABLE</refentrytitle> 13 <manvolnum>7</manvolnum> 14 <refmiscinfo>SQL - Language Statements</refmiscinfo> 15 </refmeta> 16 17 <refnamediv> 18 <refname>ALTER FOREIGN TABLE</refname> 19 <refpurpose>change the definition of a foreign table</refpurpose> 20 </refnamediv> 21 22 <refsynopsisdiv> 23<synopsis> 24ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] 25 <replaceable class="PARAMETER">action</replaceable> [, ... ] 26ALTER FOREIGN 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 FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> 29 RENAME TO <replaceable class="PARAMETER">new_name</replaceable> 30ALTER FOREIGN TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable> 31 SET SCHEMA <replaceable class="PARAMETER">new_schema</replaceable> 32 33<phrase>where <replaceable class="PARAMETER">action</replaceable> is one of:</phrase> 34 35 ADD [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] [ <replaceable class="PARAMETER">column_constraint</replaceable> [ ... ] ] 36 DROP [ COLUMN ] [ IF EXISTS ] <replaceable class="PARAMETER">column_name</replaceable> [ RESTRICT | CASCADE ] 37 ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> [ SET DATA ] TYPE <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable class="PARAMETER">collation</replaceable> ] 38 ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable> 39 ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> DROP DEFAULT 40 ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> { SET | DROP } NOT NULL 41 ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable> 42 ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] ) 43 ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] ) 44 ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN } 45 ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ]) 46 ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ] 47 VALIDATE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> 48 DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ] 49 DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ] 50 ENABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ] 51 ENABLE REPLICA TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable> 52 ENABLE ALWAYS TRIGGER <replaceable class="PARAMETER">trigger_name</replaceable> 53 SET WITH OIDS 54 SET WITHOUT OIDS 55 INHERIT <replaceable class="PARAMETER">parent_table</replaceable> 56 NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable> 57 OWNER TO { <replaceable class="PARAMETER">new_owner</replaceable> | CURRENT_USER | SESSION_USER } 58 OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ]) 59</synopsis> 60 </refsynopsisdiv> 61 62 <refsect1> 63 <title>Description</title> 64 65 <para> 66 <command>ALTER FOREIGN TABLE</command> changes the definition of an 67 existing foreign table. There are several subforms: 68 69 <variablelist> 70 <varlistentry> 71 <term><literal>ADD COLUMN</literal></term> 72 <listitem> 73 <para> 74 This form adds a new column to the foreign table, using the same syntax as 75 <xref linkend="SQL-CREATEFOREIGNTABLE">. 76 Unlike the case when adding a column to a regular table, nothing happens 77 to the underlying storage: this action simply declares that 78 some new column is now accessible through the foreign table. 79 </para> 80 </listitem> 81 </varlistentry> 82 83 <varlistentry> 84 <term><literal>DROP COLUMN [ IF EXISTS ]</literal></term> 85 <listitem> 86 <para> 87 This form drops a column from a foreign table. 88 You will need to say <literal>CASCADE</> if 89 anything outside the table depends on the column; for example, 90 views. 91 If <literal>IF EXISTS</literal> is specified and the column 92 does not exist, no error is thrown. In this case a notice 93 is issued instead. 94 </para> 95 </listitem> 96 </varlistentry> 97 98 <varlistentry> 99 <term><literal>SET DATA TYPE</literal></term> 100 <listitem> 101 <para> 102 This form changes the type of a column of a foreign table. 103 Again, this has no effect on any underlying storage: this action simply 104 changes the type that <productname>PostgreSQL</> believes the column to 105 have. 106 </para> 107 </listitem> 108 </varlistentry> 109 110 <varlistentry> 111 <term><literal>SET</literal>/<literal>DROP DEFAULT</literal></term> 112 <listitem> 113 <para> 114 These forms set or remove the default value for a column. 115 Default values only apply in subsequent <command>INSERT</command> 116 or <command>UPDATE</> commands; they do not cause rows already in the 117 table to change. 118 </para> 119 </listitem> 120 </varlistentry> 121 122 <varlistentry> 123 <term><literal>SET</literal>/<literal>DROP NOT NULL</literal></term> 124 <listitem> 125 <para> 126 Mark a column as allowing, or not allowing, null values. 127 </para> 128 </listitem> 129 </varlistentry> 130 131 <varlistentry> 132 <term><literal>SET STATISTICS</literal></term> 133 <listitem> 134 <para> 135 This form 136 sets the per-column statistics-gathering target for subsequent 137 <xref linkend="sql-analyze"> operations. 138 See the similar form of <xref linkend="sql-altertable"> 139 for more details. 140 </para> 141 </listitem> 142 </varlistentry> 143 144 <varlistentry> 145 <term><literal>SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )</literal></term> 146 <term><literal>RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )</literal></term> 147 <listitem> 148 <para> 149 This form sets or resets per-attribute options. 150 See the similar form of <xref linkend="sql-altertable"> 151 for more details. 152 </para> 153 </listitem> 154 </varlistentry> 155 156 <varlistentry> 157 <term> 158 <literal>SET STORAGE</literal> 159 </term> 160 <listitem> 161 <para> 162 This form sets the storage mode for a column. 163 See the similar form of <xref linkend="sql-altertable"> 164 for more details. 165 Note that the storage mode has no effect unless the table's 166 foreign-data wrapper chooses to pay attention to it. 167 </para> 168 </listitem> 169 </varlistentry> 170 171 <varlistentry> 172 <term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]</literal></term> 173 <listitem> 174 <para> 175 This form adds a new constraint to a foreign table, using the same 176 syntax as <xref linkend="SQL-CREATEFOREIGNTABLE">. 177 Currently only <literal>CHECK</> constraints are supported. 178 </para> 179 180 <para> 181 Unlike the case when adding a constraint to a regular table, nothing is 182 done to verify the constraint is correct; rather, this action simply 183 declares that some new condition should be assumed to hold for all rows 184 in the foreign table. (See the discussion 185 in <xref linkend="SQL-CREATEFOREIGNTABLE">.) 186 If the constraint is marked <literal>NOT VALID</>, then it isn't 187 assumed to hold, but is only recorded for possible future use. 188 </para> 189 </listitem> 190 </varlistentry> 191 192 <varlistentry> 193 <term><literal>VALIDATE CONSTRAINT</literal></term> 194 <listitem> 195 <para> 196 This form marks as valid a constraint that was previously marked 197 as <literal>NOT VALID</literal>. No action is taken to verify the 198 constraint, but future queries will assume that it holds. 199 </para> 200 </listitem> 201 </varlistentry> 202 203 <varlistentry> 204 <term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term> 205 <listitem> 206 <para> 207 This form drops the specified constraint on a foreign table. 208 If <literal>IF EXISTS</literal> is specified and the constraint 209 does not exist, no error is thrown. 210 In this case a notice is issued instead. 211 </para> 212 </listitem> 213 </varlistentry> 214 215 <varlistentry> 216 <term><literal>DISABLE</literal>/<literal>ENABLE [ REPLICA | ALWAYS ] TRIGGER</literal></term> 217 <listitem> 218 <para> 219 These forms configure the firing of trigger(s) belonging to the foreign 220 table. See the similar form of <xref linkend="sql-altertable"> for more 221 details. 222 </para> 223 </listitem> 224 </varlistentry> 225 226 <varlistentry> 227 <term><literal>SET WITH OIDS</literal></term> 228 <listitem> 229 <para> 230 This form adds an <literal>oid</literal> system column to the 231 table (see <xref linkend="ddl-system-columns">). 232 It does nothing if the table already has OIDs. 233 Unless the table's foreign-data wrapper supports OIDs, this column 234 will simply read as zeroes. 235 </para> 236 237 <para> 238 Note that this is not equivalent to <literal>ADD COLUMN oid oid</>; 239 that would add a normal column that happened to be named 240 <literal>oid</>, not a system column. 241 </para> 242 </listitem> 243 </varlistentry> 244 245 <varlistentry> 246 <term><literal>SET WITHOUT OIDS</literal></term> 247 <listitem> 248 <para> 249 This form removes the <literal>oid</literal> system column from the 250 table. This is exactly equivalent to 251 <literal>DROP COLUMN oid RESTRICT</literal>, 252 except that it will not complain if there is already no 253 <literal>oid</literal> column. 254 </para> 255 </listitem> 256 </varlistentry> 257 258 <varlistentry> 259 <term><literal>INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term> 260 <listitem> 261 <para> 262 This form adds the target foreign table as a new child of the specified 263 parent table. 264 See the similar form of <xref linkend="sql-altertable"> 265 for more details. 266 </para> 267 </listitem> 268 </varlistentry> 269 270 <varlistentry> 271 <term><literal>NO INHERIT <replaceable class="PARAMETER">parent_table</replaceable></literal></term> 272 <listitem> 273 <para> 274 This form removes the target foreign table from the list of children of 275 the specified parent table. 276 </para> 277 </listitem> 278 </varlistentry> 279 280 <varlistentry> 281 <term><literal>OWNER</literal></term> 282 <listitem> 283 <para> 284 This form changes the owner of the foreign table to the 285 specified user. 286 </para> 287 </listitem> 288 </varlistentry> 289 290 <varlistentry> 291 <term><literal>OPTIONS ( [ ADD | SET | DROP ] <replaceable class="PARAMETER">option</replaceable> ['<replaceable class="PARAMETER">value</replaceable>'] [, ... ] )</literal></term> 292 <listitem> 293 <para> 294 Change options for the foreign table or one of its columns. 295 <literal>ADD</>, <literal>SET</>, and <literal>DROP</> 296 specify the action to be performed. <literal>ADD</> is assumed 297 if no operation is explicitly specified. Duplicate option names are not 298 allowed (although it's OK for a table option and a column option to have 299 the same name). Option names and values are also validated using the 300 foreign data wrapper library. 301 </para> 302 </listitem> 303 </varlistentry> 304 305 <varlistentry> 306 <term><literal>RENAME</literal></term> 307 <listitem> 308 <para> 309 The <literal>RENAME</literal> forms change the name of a foreign table 310 or the name of an individual column in a foreign table. 311 </para> 312 </listitem> 313 </varlistentry> 314 315 <varlistentry> 316 <term><literal>SET SCHEMA</literal></term> 317 <listitem> 318 <para> 319 This form moves the foreign table into another schema. 320 </para> 321 </listitem> 322 </varlistentry> 323 324 </variablelist> 325 </para> 326 327 <para> 328 All the actions except <literal>RENAME</literal> and <literal>SET SCHEMA</> 329 can be combined into 330 a list of multiple alterations to apply in parallel. For example, it 331 is possible to add several columns and/or alter the type of several 332 columns in a single command. 333 </para> 334 335 <para> 336 If the command is written as <literal>ALTER FOREIGN TABLE IF EXISTS ...</> 337 and the foreign table does not exist, no error is thrown. A notice is 338 issued in this case. 339 </para> 340 341 <para> 342 You must own the table to use <command>ALTER FOREIGN TABLE</>. 343 To change the schema of a foreign table, you must also have 344 <literal>CREATE</literal> privilege on the new schema. 345 To alter the owner, you must also be a direct or indirect member of the new 346 owning role, and that role must have <literal>CREATE</literal> privilege on 347 the table's schema. (These restrictions enforce that altering the owner 348 doesn't do anything you couldn't do by dropping and recreating the table. 349 However, a superuser can alter ownership of any table anyway.) 350 To add a column or alter a column type, you must also 351 have <literal>USAGE</literal> privilege on the data type. 352 </para> 353 </refsect1> 354 355 <refsect1> 356 <title>Parameters</title> 357 358 <variablelist> 359 360 <varlistentry> 361 <term><replaceable class="PARAMETER">name</replaceable></term> 362 <listitem> 363 <para> 364 The name (possibly schema-qualified) of an existing foreign table to 365 alter. If <literal>ONLY</> is specified before the table name, only 366 that table is altered. If <literal>ONLY</> is not specified, the table 367 and all its descendant tables (if any) are altered. Optionally, 368 <literal>*</> can be specified after the table name to explicitly 369 indicate that descendant tables are included. 370 </para> 371 </listitem> 372 </varlistentry> 373 374 <varlistentry> 375 <term><replaceable class="PARAMETER">column_name</replaceable></term> 376 <listitem> 377 <para> 378 Name of a new or existing column. 379 </para> 380 </listitem> 381 </varlistentry> 382 383 <varlistentry> 384 <term><replaceable class="PARAMETER">new_column_name</replaceable></term> 385 <listitem> 386 <para> 387 New name for an existing column. 388 </para> 389 </listitem> 390 </varlistentry> 391 392 <varlistentry> 393 <term><replaceable class="PARAMETER">new_name</replaceable></term> 394 <listitem> 395 <para> 396 New name for the table. 397 </para> 398 </listitem> 399 </varlistentry> 400 401 <varlistentry> 402 <term><replaceable class="PARAMETER">data_type</replaceable></term> 403 <listitem> 404 <para> 405 Data type of the new column, or new data type for an existing 406 column. 407 </para> 408 </listitem> 409 </varlistentry> 410 411 <varlistentry> 412 <term><replaceable class="PARAMETER">table_constraint</replaceable></term> 413 <listitem> 414 <para> 415 New table constraint for the foreign table. 416 </para> 417 </listitem> 418 </varlistentry> 419 420 <varlistentry> 421 <term><replaceable class="PARAMETER">constraint_name</replaceable></term> 422 <listitem> 423 <para> 424 Name of an existing constraint to drop. 425 </para> 426 </listitem> 427 </varlistentry> 428 429 <varlistentry> 430 <term><literal>CASCADE</literal></term> 431 <listitem> 432 <para> 433 Automatically drop objects that depend on the dropped column 434 or constraint (for example, views referencing the column), 435 and in turn all objects that depend on those objects 436 (see <xref linkend="ddl-depend">). 437 </para> 438 </listitem> 439 </varlistentry> 440 441 <varlistentry> 442 <term><literal>RESTRICT</literal></term> 443 <listitem> 444 <para> 445 Refuse to drop the column or constraint if there are any dependent 446 objects. This is the default behavior. 447 </para> 448 </listitem> 449 </varlistentry> 450 451 <varlistentry> 452 <term><replaceable class="PARAMETER">trigger_name</replaceable></term> 453 <listitem> 454 <para> 455 Name of a single trigger to disable or enable. 456 </para> 457 </listitem> 458 </varlistentry> 459 460 <varlistentry> 461 <term><literal>ALL</literal></term> 462 <listitem> 463 <para> 464 Disable or enable all triggers belonging to the foreign table. (This 465 requires superuser privilege if any of the triggers are internally 466 generated triggers. The core system does not add such triggers to 467 foreign tables, but add-on code could do so.) 468 </para> 469 </listitem> 470 </varlistentry> 471 472 <varlistentry> 473 <term><literal>USER</literal></term> 474 <listitem> 475 <para> 476 Disable or enable all triggers belonging to the foreign table except 477 for internally generated triggers. 478 </para> 479 </listitem> 480 </varlistentry> 481 482 <varlistentry> 483 <term><replaceable class="PARAMETER">parent_table</replaceable></term> 484 <listitem> 485 <para> 486 A parent table to associate or de-associate with this foreign table. 487 </para> 488 </listitem> 489 </varlistentry> 490 491 <varlistentry> 492 <term><replaceable class="PARAMETER">new_owner</replaceable></term> 493 <listitem> 494 <para> 495 The user name of the new owner of the table. 496 </para> 497 </listitem> 498 </varlistentry> 499 500 <varlistentry> 501 <term><replaceable class="PARAMETER">new_schema</replaceable></term> 502 <listitem> 503 <para> 504 The name of the schema to which the table will be moved. 505 </para> 506 </listitem> 507 </varlistentry> 508 </variablelist> 509 </refsect1> 510 511 <refsect1> 512 <title>Notes</title> 513 514 <para> 515 The key word <literal>COLUMN</literal> is noise and can be omitted. 516 </para> 517 518 <para> 519 Consistency with the foreign server is not checked when a column is added 520 or removed with <literal>ADD COLUMN</literal> or 521 <literal>DROP COLUMN</literal>, a <literal>NOT NULL</> 522 or <literal>CHECK</> constraint is added, or a column type is changed 523 with <literal>SET DATA TYPE</>. It is the user's responsibility to ensure 524 that the table definition matches the remote side. 525 </para> 526 527 <para> 528 Refer to <xref linkend="sql-createforeigntable"> for a further description of valid 529 parameters. 530 </para> 531 </refsect1> 532 533 <refsect1> 534 <title>Examples</title> 535 536 <para> 537 To mark a column as not-null: 538<programlisting> 539ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL; 540</programlisting> 541 </para> 542 543 <para> 544 To change options of a foreign table: 545<programlisting> 546ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3'); 547</programlisting></para> 548 549 </refsect1> 550 551 <refsect1> 552 <title>Compatibility</title> 553 554 <para> 555 The forms <literal>ADD</literal>, <literal>DROP</>, 556 and <literal>SET DATA TYPE</literal> 557 conform with the SQL standard. The other forms are 558 <productname>PostgreSQL</productname> extensions of the SQL standard. 559 Also, the ability to specify more than one manipulation in a single 560 <command>ALTER FOREIGN TABLE</> command is an extension. 561 </para> 562 563 <para> 564 <command>ALTER FOREIGN TABLE DROP COLUMN</> can be used to drop the only 565 column of a foreign table, leaving a zero-column table. This is an 566 extension of SQL, which disallows zero-column foreign tables. 567 </para> 568 </refsect1> 569 570 <refsect1> 571 <title>See Also</title> 572 573 <simplelist type="inline"> 574 <member><xref linkend="sql-createforeigntable"></member> 575 <member><xref linkend="sql-dropforeigntable"></member> 576 </simplelist> 577 </refsect1> 578</refentry> 579