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