Debian Codegraph @

doc/src/sgml/ref/alter_foreign_table.sgml

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