1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
|
<?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 id="docContent" class="container-fluid col-10"><div 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 16.2 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 /></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 | DEFAULT }
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 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_ROLE | 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="link" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><code class="command">CREATE FOREIGN TABLE</code></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="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> operations.
See the similar form of <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></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="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></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="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></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="link" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><code class="command">CREATE FOREIGN TABLE</code></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="link" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><code class="command">CREATE FOREIGN TABLE</code></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="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></a> for more
details.
</p></dd><dt><span class="term"><code class="literal">SET WITHOUT OIDS</code></span></dt><dd><p>
Backward compatibility syntax for removing the <code class="literal">oid</code>
system column. As <code class="literal">oid</code> system columns cannot be added
anymore, this never has an effect.
</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="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></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 be able to <code class="literal">SET ROLE</code> to 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.14. Dependency Tracking">Section 5.14</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="link" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><code class="command">CREATE FOREIGN TABLE</code></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);
</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" title="ALTER FOREIGN DATA WRAPPER">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-alterfunction.html" title="ALTER FUNCTION">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" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER FUNCTION</td></tr></table></div></body></html>
|
