<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>ALTER FOREIGN TABLE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-alterforeigndatawrapper.html" title="ALTER FOREIGN DATA WRAPPER" /><link rel="next" href="sql-alterfunction.html" title="ALTER FUNCTION" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">ALTER FOREIGN TABLE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-alterforeigndatawrapper.html" title="ALTER FOREIGN DATA WRAPPER">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-alterfunction.html" title="ALTER FUNCTION">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-ALTERFOREIGNTABLE"><div class="titlepage"></div><a id="id-1.9.3.13.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER FOREIGN TABLE</span></h2><p>ALTER FOREIGN TABLE — change the definition of a foreign table</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] <em class="replaceable"><code>name</code></em> [ * ]
<em class="replaceable"><code>action</code></em> [, ... ]
ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] <em class="replaceable"><code>name</code></em> [ * ]
RENAME [ COLUMN ] <em class="replaceable"><code>column_name</code></em> TO <em class="replaceable"><code>new_column_name</code></em>
ALTER FOREIGN TABLE [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
RENAME TO <em class="replaceable"><code>new_name</code></em>
ALTER FOREIGN TABLE [ IF EXISTS ] <em class="replaceable"><code>name</code></em>
SET SCHEMA <em class="replaceable"><code>new_schema</code></em>
<span class="phrase">where <em class="replaceable"><code>action</code></em> is one of:</span>
ADD [ COLUMN ] <em class="replaceable"><code>column_name</code></em> <em class="replaceable"><code>data_type</code></em> [ COLLATE <em class="replaceable"><code>collation</code></em> ] [ <em class="replaceable"><code>column_constraint</code></em> [ ... ] ]
DROP [ COLUMN ] [ IF EXISTS ] <em class="replaceable"><code>column_name</code></em> [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> [ SET DATA ] TYPE <em class="replaceable"><code>data_type</code></em> [ COLLATE <em class="replaceable"><code>collation</code></em> ]
ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET DEFAULT <em class="replaceable"><code>expression</code></em>
ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> DROP DEFAULT
ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> { SET | DROP } NOT NULL
ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET STATISTICS <em class="replaceable"><code>integer</code></em>
ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET ( <em class="replaceable"><code>attribute_option</code></em> = <em class="replaceable"><code>value</code></em> [, ... ] )
ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> RESET ( <em class="replaceable"><code>attribute_option</code></em> [, ... ] )
ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> OPTIONS ( [ ADD | SET | DROP ] <em class="replaceable"><code>option</code></em> ['<em class="replaceable"><code>value</code></em>'] [, ... ])
ADD <em class="replaceable"><code>table_constraint</code></em> [ NOT VALID ]
VALIDATE CONSTRAINT <em class="replaceable"><code>constraint_name</code></em>
DROP CONSTRAINT [ IF EXISTS ] <em class="replaceable"><code>constraint_name</code></em> [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ <em class="replaceable"><code>trigger_name</code></em> | ALL | USER ]
ENABLE TRIGGER [ <em class="replaceable"><code>trigger_name</code></em> | ALL | USER ]
ENABLE REPLICA TRIGGER <em class="replaceable"><code>trigger_name</code></em>
ENABLE ALWAYS TRIGGER <em class="replaceable"><code>trigger_name</code></em>
SET WITH OIDS
SET WITHOUT OIDS
INHERIT <em class="replaceable"><code>parent_table</code></em>
NO INHERIT <em class="replaceable"><code>parent_table</code></em>
OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_USER | SESSION_USER }
OPTIONS ( [ ADD | SET | DROP ] <em class="replaceable"><code>option</code></em> ['<em class="replaceable"><code>value</code></em>'] [, ... ])
</pre></div><div class="refsect1" id="id-1.9.3.13.5"><h2>Description</h2><p>
<code class="command">ALTER FOREIGN TABLE</code> changes the definition of an
existing foreign table. There are several subforms:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">ADD COLUMN</code></span></dt><dd><p>
This form adds a new column to the foreign table, using the same syntax as
<a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>.
Unlike the case when adding a column to a regular table, nothing happens
to the underlying storage: this action simply declares that
some new column is now accessible through the foreign table.
</p></dd><dt><span class="term"><code class="literal">DROP COLUMN [ IF EXISTS ]</code></span></dt><dd><p>
This form drops a column from a foreign table.
You will need to say <code class="literal">CASCADE</code> if
anything outside the table depends on the column; for example,
views.
If <code class="literal">IF EXISTS</code> is specified and the column
does not exist, no error is thrown. In this case a notice
is issued instead.
</p></dd><dt><span class="term"><code class="literal">SET DATA TYPE</code></span></dt><dd><p>
This form changes the type of a column of a foreign table.
Again, this has no effect on any underlying storage: this action simply
changes the type that <span class="productname">PostgreSQL</span> believes the column to
have.
</p></dd><dt><span class="term"><code class="literal">SET</code>/<code class="literal">DROP DEFAULT</code></span></dt><dd><p>
These forms set or remove the default value for a column.
Default values only apply in subsequent <code class="command">INSERT</code>
or <code class="command">UPDATE</code> commands; they do not cause rows already in the
table to change.
</p></dd><dt><span class="term"><code class="literal">SET</code>/<code class="literal">DROP NOT NULL</code></span></dt><dd><p>
Mark a column as allowing, or not allowing, null values.
</p></dd><dt><span class="term"><code class="literal">SET STATISTICS</code></span></dt><dd><p>
This form
sets the per-column statistics-gathering target for subsequent
<a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> operations.
See the similar form of <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>
for more details.
</p></dd><dt><span class="term"><code class="literal">SET ( <em class="replaceable"><code>attribute_option</code></em> = <em class="replaceable"><code>value</code></em> [, ... ] )</code><br /></span><span class="term"><code class="literal">RESET ( <em class="replaceable"><code>attribute_option</code></em> [, ... ] )</code></span></dt><dd><p>
This form sets or resets per-attribute options.
See the similar form of <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>
for more details.
</p></dd><dt><span class="term">
<code class="literal">SET STORAGE</code>
</span></dt><dd><p>
This form sets the storage mode for a column.
See the similar form of <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>
for more details.
Note that the storage mode has no effect unless the table's
foreign-data wrapper chooses to pay attention to it.
</p></dd><dt><span class="term"><code class="literal">ADD <em class="replaceable"><code>table_constraint</code></em> [ NOT VALID ]</code></span></dt><dd><p>
This form adds a new constraint to a foreign table, using the same
syntax as <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>.
Currently only <code class="literal">CHECK</code> constraints are supported.
</p><p>
Unlike the case when adding a constraint to a regular table, nothing is
done to verify the constraint is correct; rather, this action simply
declares that some new condition should be assumed to hold for all rows
in the foreign table. (See the discussion
in <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>.)
If the constraint is marked <code class="literal">NOT VALID</code>, then it isn't
assumed to hold, but is only recorded for possible future use.
</p></dd><dt><span class="term"><code class="literal">VALIDATE CONSTRAINT</code></span></dt><dd><p>
This form marks as valid a constraint that was previously marked
as <code class="literal">NOT VALID</code>. No action is taken to verify the
constraint, but future queries will assume that it holds.
</p></dd><dt><span class="term"><code class="literal">DROP CONSTRAINT [ IF EXISTS ]</code></span></dt><dd><p>
This form drops the specified constraint on a foreign table.
If <code class="literal">IF EXISTS</code> is specified and the constraint
does not exist, no error is thrown.
In this case a notice is issued instead.
</p></dd><dt><span class="term"><code class="literal">DISABLE</code>/<code class="literal">ENABLE [ REPLICA | ALWAYS ] TRIGGER</code></span></dt><dd><p>
These forms configure the firing of trigger(s) belonging to the foreign
table. See the similar form of <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a> for more
details.
</p></dd><dt><span class="term"><code class="literal">SET WITH OIDS</code></span></dt><dd><p>
This form adds an <code class="literal">oid</code> system column to the
table (see <a class="xref" href="ddl-system-columns.html" title="5.4. System Columns">Section 5.4</a>).
It does nothing if the table already has OIDs.
Unless the table's foreign-data wrapper supports OIDs, this column
will simply read as zeroes.
</p><p>
Note that this is not equivalent to <code class="literal">ADD COLUMN oid oid</code>;
that would add a normal column that happened to be named
<code class="literal">oid</code>, not a system column.
</p></dd><dt><span class="term"><code class="literal">SET WITHOUT OIDS</code></span></dt><dd><p>
This form removes the <code class="literal">oid</code> system column from the
table. This is exactly equivalent to
<code class="literal">DROP COLUMN oid RESTRICT</code>,
except that it will not complain if there is already no
<code class="literal">oid</code> column.
</p></dd><dt><span class="term"><code class="literal">INHERIT <em class="replaceable"><code>parent_table</code></em></code></span></dt><dd><p>
This form adds the target foreign table as a new child of the specified
parent table.
See the similar form of <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>
for more details.
</p></dd><dt><span class="term"><code class="literal">NO INHERIT <em class="replaceable"><code>parent_table</code></em></code></span></dt><dd><p>
This form removes the target foreign table from the list of children of
the specified parent table.
</p></dd><dt><span class="term"><code class="literal">OWNER</code></span></dt><dd><p>
This form changes the owner of the foreign table to the
specified user.
</p></dd><dt><span class="term"><code class="literal">OPTIONS ( [ ADD | SET | DROP ] <em class="replaceable"><code>option</code></em> ['<em class="replaceable"><code>value</code></em>'] [, ... ] )</code></span></dt><dd><p>
Change options for the foreign table or one of its columns.
<code class="literal">ADD</code>, <code class="literal">SET</code>, and <code class="literal">DROP</code>
specify the action to be performed. <code class="literal">ADD</code> is assumed
if no operation is explicitly specified. Duplicate option names are not
allowed (although it's OK for a table option and a column option to have
the same name). Option names and values are also validated using the
foreign data wrapper library.
</p></dd><dt><span class="term"><code class="literal">RENAME</code></span></dt><dd><p>
The <code class="literal">RENAME</code> forms change the name of a foreign table
or the name of an individual column in a foreign table.
</p></dd><dt><span class="term"><code class="literal">SET SCHEMA</code></span></dt><dd><p>
This form moves the foreign table into another schema.
</p></dd></dl></div><p>
</p><p>
All the actions except <code class="literal">RENAME</code> and <code class="literal">SET SCHEMA</code>
can be combined into
a list of multiple alterations to apply in parallel. For example, it
is possible to add several columns and/or alter the type of several
columns in a single command.
</p><p>
If the command is written as <code class="literal">ALTER FOREIGN TABLE IF EXISTS ...</code>
and the foreign table does not exist, no error is thrown. A notice is
issued in this case.
</p><p>
You must own the table to use <code class="command">ALTER FOREIGN TABLE</code>.
To change the schema of a foreign table, you must also have
<code class="literal">CREATE</code> privilege on the new schema.
To alter the owner, you must also be a direct or indirect member of the new
owning role, and that role must have <code class="literal">CREATE</code> privilege on
the table's schema. (These restrictions enforce that altering the owner
doesn't do anything you couldn't do by dropping and recreating the table.
However, a superuser can alter ownership of any table anyway.)
To add a column or alter a column type, you must also
have <code class="literal">USAGE</code> privilege on the data type.
</p></div><div class="refsect1" id="id-1.9.3.13.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
The name (possibly schema-qualified) of an existing foreign table to
alter. If <code class="literal">ONLY</code> is specified before the table name, only
that table is altered. If <code class="literal">ONLY</code> is not specified, the table
and all its descendant tables (if any) are altered. Optionally,
<code class="literal">*</code> can be specified after the table name to explicitly
indicate that descendant tables are included.
</p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>
Name of a new or existing column.
</p></dd><dt><span class="term"><em class="replaceable"><code>new_column_name</code></em></span></dt><dd><p>
New name for an existing column.
</p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p>
New name for the table.
</p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p>
Data type of the new column, or new data type for an existing
column.
</p></dd><dt><span class="term"><em class="replaceable"><code>table_constraint</code></em></span></dt><dd><p>
New table constraint for the foreign table.
</p></dd><dt><span class="term"><em class="replaceable"><code>constraint_name</code></em></span></dt><dd><p>
Name of an existing constraint to drop.
</p></dd><dt><span class="term"><code class="literal">CASCADE</code></span></dt><dd><p>
Automatically drop objects that depend on the dropped column
or constraint (for example, views referencing the column),
and in turn all objects that depend on those objects
(see <a class="xref" href="ddl-depend.html" title="5.13. Dependency Tracking">Section 5.13</a>).
</p></dd><dt><span class="term"><code class="literal">RESTRICT</code></span></dt><dd><p>
Refuse to drop the column or constraint if there are any dependent
objects. This is the default behavior.
</p></dd><dt><span class="term"><em class="replaceable"><code>trigger_name</code></em></span></dt><dd><p>
Name of a single trigger to disable or enable.
</p></dd><dt><span class="term"><code class="literal">ALL</code></span></dt><dd><p>
Disable or enable all triggers belonging to the foreign table. (This
requires superuser privilege if any of the triggers are internally
generated triggers. The core system does not add such triggers to
foreign tables, but add-on code could do so.)
</p></dd><dt><span class="term"><code class="literal">USER</code></span></dt><dd><p>
Disable or enable all triggers belonging to the foreign table except
for internally generated triggers.
</p></dd><dt><span class="term"><em class="replaceable"><code>parent_table</code></em></span></dt><dd><p>
A parent table to associate or de-associate with this foreign table.
</p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p>
The user name of the new owner of the table.
</p></dd><dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt><dd><p>
The name of the schema to which the table will be moved.
</p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.13.7"><h2>Notes</h2><p>
The key word <code class="literal">COLUMN</code> is noise and can be omitted.
</p><p>
Consistency with the foreign server is not checked when a column is added
or removed with <code class="literal">ADD COLUMN</code> or
<code class="literal">DROP COLUMN</code>, a <code class="literal">NOT NULL</code>
or <code class="literal">CHECK</code> constraint is added, or a column type is changed
with <code class="literal">SET DATA TYPE</code>. It is the user's responsibility to ensure
that the table definition matches the remote side.
</p><p>
Refer to <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a> for a further description of valid
parameters.
</p></div><div class="refsect1" id="id-1.9.3.13.8"><h2>Examples</h2><p>
To mark a column as not-null:
</p><pre class="programlisting">
ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
</pre><p>
</p><p>
To change options of a foreign table:
</p><pre class="programlisting">
ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3');
</pre></div><div class="refsect1" id="id-1.9.3.13.9"><h2>Compatibility</h2><p>
The forms <code class="literal">ADD</code>, <code class="literal">DROP</code>,
and <code class="literal">SET DATA TYPE</code>
conform with the SQL standard. The other forms are
<span class="productname">PostgreSQL</span> extensions of the SQL standard.
Also, the ability to specify more than one manipulation in a single
<code class="command">ALTER FOREIGN TABLE</code> command is an extension.
</p><p>
<code class="command">ALTER FOREIGN TABLE DROP COLUMN</code> can be used to drop the only
column of a foreign table, leaving a zero-column table. This is an
extension of SQL, which disallows zero-column foreign tables.
</p></div><div class="refsect1" id="id-1.9.3.13.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>, <a class="xref" href="sql-dropforeigntable.html" title="DROP FOREIGN TABLE"><span class="refentrytitle">DROP FOREIGN TABLE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-alterforeigndatawrapper.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-alterfunction.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER FOREIGN DATA WRAPPER </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> ALTER FUNCTION</td></tr></table></div></body></html>
