codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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">3ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] <em class="replaceable"><code>name</code></em> [ * ]4 <em class="replaceable"><code>action</code></em> [, ... ]5ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] <em class="replaceable"><code>name</code></em> [ * ]6 RENAME [ COLUMN ] <em class="replaceable"><code>column_name</code></em> TO <em class="replaceable"><code>new_column_name</code></em>7ALTER FOREIGN TABLE [ IF EXISTS ] <em class="replaceable"><code>name</code></em>8 RENAME TO <em class="replaceable"><code>new_name</code></em>9ALTER FOREIGN TABLE [ IF EXISTS ] <em class="replaceable"><code>name</code></em>10 SET SCHEMA <em class="replaceable"><code>new_schema</code></em>11 12<span class="phrase">where <em class="replaceable"><code>action</code></em> is one of:</span>13 14 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> [ ... ] ]15 DROP [ COLUMN ] [ IF EXISTS ] <em class="replaceable"><code>column_name</code></em> [ RESTRICT | CASCADE ]16 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> ]17 ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET DEFAULT <em class="replaceable"><code>expression</code></em>18 ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> DROP DEFAULT19 ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> { SET | DROP } NOT NULL20 ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET STATISTICS <em class="replaceable"><code>integer</code></em>21 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> [, ... ] )22 ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> RESET ( <em class="replaceable"><code>attribute_option</code></em> [, ... ] )23 ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }24 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>'] [, ... ])25 ADD <em class="replaceable"><code>table_constraint</code></em> [ NOT VALID ]26 VALIDATE CONSTRAINT <em class="replaceable"><code>constraint_name</code></em>27 DROP CONSTRAINT [ IF EXISTS ] <em class="replaceable"><code>constraint_name</code></em> [ RESTRICT | CASCADE ]28 DISABLE TRIGGER [ <em class="replaceable"><code>trigger_name</code></em> | ALL | USER ]29 ENABLE TRIGGER [ <em class="replaceable"><code>trigger_name</code></em> | ALL | USER ]30 ENABLE REPLICA TRIGGER <em class="replaceable"><code>trigger_name</code></em>31 ENABLE ALWAYS TRIGGER <em class="replaceable"><code>trigger_name</code></em>32 SET WITHOUT OIDS33 INHERIT <em class="replaceable"><code>parent_table</code></em>34 NO INHERIT <em class="replaceable"><code>parent_table</code></em>35 OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }36 OPTIONS ( [ ADD | SET | DROP ] <em class="replaceable"><code>option</code></em> ['<em class="replaceable"><code>value</code></em>'] [, ... ])37</pre></div><div class="refsect1" id="id-1.9.3.13.5"><h2>Description</h2><p>38 <code class="command">ALTER FOREIGN TABLE</code> changes the definition of an39 existing foreign table. There are several subforms:40 41 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">ADD COLUMN</code></span></dt><dd><p>42 This form adds a new column to the foreign table, using the same syntax as43 <a class="link" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><code class="command">CREATE FOREIGN TABLE</code></a>.44 Unlike the case when adding a column to a regular table, nothing happens45 to the underlying storage: this action simply declares that46 some new column is now accessible through the foreign table.47 </p></dd><dt><span class="term"><code class="literal">DROP COLUMN [ IF EXISTS ]</code></span></dt><dd><p>48 This form drops a column from a foreign table.49 You will need to say <code class="literal">CASCADE</code> if50 anything outside the table depends on the column; for example,51 views.52 If <code class="literal">IF EXISTS</code> is specified and the column53 does not exist, no error is thrown. In this case a notice54 is issued instead.55 </p></dd><dt><span class="term"><code class="literal">SET DATA TYPE</code></span></dt><dd><p>56 This form changes the type of a column of a foreign table.57 Again, this has no effect on any underlying storage: this action simply58 changes the type that <span class="productname">PostgreSQL</span> believes the column to59 have.60 </p></dd><dt><span class="term"><code class="literal">SET</code>/<code class="literal">DROP DEFAULT</code></span></dt><dd><p>61 These forms set or remove the default value for a column.62 Default values only apply in subsequent <code class="command">INSERT</code>63 or <code class="command">UPDATE</code> commands; they do not cause rows already in the64 table to change.65 </p></dd><dt><span class="term"><code class="literal">SET</code>/<code class="literal">DROP NOT NULL</code></span></dt><dd><p>66 Mark a column as allowing, or not allowing, null values.67 </p></dd><dt><span class="term"><code class="literal">SET STATISTICS</code></span></dt><dd><p>68 This form69 sets the per-column statistics-gathering target for subsequent70 <a class="link" href="sql-analyze.html" title="ANALYZE"><code class="command">ANALYZE</code></a> operations.71 See the similar form of <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></a>72 for more details.73 </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>74 This form sets or resets per-attribute options.75 See the similar form of <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></a>76 for more details.77 </p></dd><dt><span class="term">78 <code class="literal">SET STORAGE</code>79 </span></dt><dd><p>80 This form sets the storage mode for a column.81 See the similar form of <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></a>82 for more details.83 Note that the storage mode has no effect unless the table's84 foreign-data wrapper chooses to pay attention to it.85 </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>86 This form adds a new constraint to a foreign table, using the same87 syntax as <a class="link" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><code class="command">CREATE FOREIGN TABLE</code></a>.88 Currently only <code class="literal">CHECK</code> constraints are supported.89 </p><p>90 Unlike the case when adding a constraint to a regular table, nothing is91 done to verify the constraint is correct; rather, this action simply92 declares that some new condition should be assumed to hold for all rows93 in the foreign table. (See the discussion94 in <a class="link" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><code class="command">CREATE FOREIGN TABLE</code></a>.)95 If the constraint is marked <code class="literal">NOT VALID</code>, then it isn't96 assumed to hold, but is only recorded for possible future use.97 </p></dd><dt><span class="term"><code class="literal">VALIDATE CONSTRAINT</code></span></dt><dd><p>98 This form marks as valid a constraint that was previously marked99 as <code class="literal">NOT VALID</code>. No action is taken to verify the100 constraint, but future queries will assume that it holds.101 </p></dd><dt><span class="term"><code class="literal">DROP CONSTRAINT [ IF EXISTS ]</code></span></dt><dd><p>102 This form drops the specified constraint on a foreign table.103 If <code class="literal">IF EXISTS</code> is specified and the constraint104 does not exist, no error is thrown.105 In this case a notice is issued instead.106 </p></dd><dt><span class="term"><code class="literal">DISABLE</code>/<code class="literal">ENABLE [ REPLICA | ALWAYS ] TRIGGER</code></span></dt><dd><p>107 These forms configure the firing of trigger(s) belonging to the foreign108 table. See the similar form of <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></a> for more109 details.110 </p></dd><dt><span class="term"><code class="literal">SET WITHOUT OIDS</code></span></dt><dd><p>111 Backward compatibility syntax for removing the <code class="literal">oid</code>112 system column. As <code class="literal">oid</code> system columns cannot be added113 anymore, this never has an effect.114 </p></dd><dt><span class="term"><code class="literal">INHERIT <em class="replaceable"><code>parent_table</code></em></code></span></dt><dd><p>115 This form adds the target foreign table as a new child of the specified116 parent table.117 See the similar form of <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE</code></a>118 for more details.119 </p></dd><dt><span class="term"><code class="literal">NO INHERIT <em class="replaceable"><code>parent_table</code></em></code></span></dt><dd><p>120 This form removes the target foreign table from the list of children of121 the specified parent table.122 </p></dd><dt><span class="term"><code class="literal">OWNER</code></span></dt><dd><p>123 This form changes the owner of the foreign table to the124 specified user.125 </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>126 Change options for the foreign table or one of its columns.127 <code class="literal">ADD</code>, <code class="literal">SET</code>, and <code class="literal">DROP</code>128 specify the action to be performed. <code class="literal">ADD</code> is assumed129 if no operation is explicitly specified. Duplicate option names are not130 allowed (although it's OK for a table option and a column option to have131 the same name). Option names and values are also validated using the132 foreign data wrapper library.133 </p></dd><dt><span class="term"><code class="literal">RENAME</code></span></dt><dd><p>134 The <code class="literal">RENAME</code> forms change the name of a foreign table135 or the name of an individual column in a foreign table.136 </p></dd><dt><span class="term"><code class="literal">SET SCHEMA</code></span></dt><dd><p>137 This form moves the foreign table into another schema.138 </p></dd></dl></div><p>139 </p><p>140 All the actions except <code class="literal">RENAME</code> and <code class="literal">SET SCHEMA</code>141 can be combined into142 a list of multiple alterations to apply in parallel. For example, it143 is possible to add several columns and/or alter the type of several144 columns in a single command.145 </p><p>146 If the command is written as <code class="literal">ALTER FOREIGN TABLE IF EXISTS ...</code>147 and the foreign table does not exist, no error is thrown. A notice is148 issued in this case.149 </p><p>150 You must own the table to use <code class="command">ALTER FOREIGN TABLE</code>.151 To change the schema of a foreign table, you must also have152 <code class="literal">CREATE</code> privilege on the new schema.153 To alter the owner, you must be able to <code class="literal">SET ROLE</code> to the154 new owning role, and that role must have <code class="literal">CREATE</code> privilege155 on the table's schema. (These restrictions enforce that altering the owner156 doesn't do anything you couldn't do by dropping and recreating the table.157 However, a superuser can alter ownership of any table anyway.)158 To add a column or alter a column type, you must also159 have <code class="literal">USAGE</code> privilege on the data type.160 </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>161 The name (possibly schema-qualified) of an existing foreign table to162 alter. If <code class="literal">ONLY</code> is specified before the table name, only163 that table is altered. If <code class="literal">ONLY</code> is not specified, the table164 and all its descendant tables (if any) are altered. Optionally,165 <code class="literal">*</code> can be specified after the table name to explicitly166 indicate that descendant tables are included.167 </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>168 Name of a new or existing column.169 </p></dd><dt><span class="term"><em class="replaceable"><code>new_column_name</code></em></span></dt><dd><p>170 New name for an existing column.171 </p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p>172 New name for the table.173 </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p>174 Data type of the new column, or new data type for an existing175 column.176 </p></dd><dt><span class="term"><em class="replaceable"><code>table_constraint</code></em></span></dt><dd><p>177 New table constraint for the foreign table.178 </p></dd><dt><span class="term"><em class="replaceable"><code>constraint_name</code></em></span></dt><dd><p>179 Name of an existing constraint to drop.180 </p></dd><dt><span class="term"><code class="literal">CASCADE</code></span></dt><dd><p>181 Automatically drop objects that depend on the dropped column182 or constraint (for example, views referencing the column),183 and in turn all objects that depend on those objects184 (see <a class="xref" href="ddl-depend.html" title="5.14. Dependency Tracking">Section 5.14</a>).185 </p></dd><dt><span class="term"><code class="literal">RESTRICT</code></span></dt><dd><p>186 Refuse to drop the column or constraint if there are any dependent187 objects. This is the default behavior.188 </p></dd><dt><span class="term"><em class="replaceable"><code>trigger_name</code></em></span></dt><dd><p>189 Name of a single trigger to disable or enable.190 </p></dd><dt><span class="term"><code class="literal">ALL</code></span></dt><dd><p>191 Disable or enable all triggers belonging to the foreign table. (This192 requires superuser privilege if any of the triggers are internally193 generated triggers. The core system does not add such triggers to194 foreign tables, but add-on code could do so.)195 </p></dd><dt><span class="term"><code class="literal">USER</code></span></dt><dd><p>196 Disable or enable all triggers belonging to the foreign table except197 for internally generated triggers.198 </p></dd><dt><span class="term"><em class="replaceable"><code>parent_table</code></em></span></dt><dd><p>199 A parent table to associate or de-associate with this foreign table.200 </p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p>201 The user name of the new owner of the table.202 </p></dd><dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt><dd><p>203 The name of the schema to which the table will be moved.204 </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.13.7"><h2>Notes</h2><p>205 The key word <code class="literal">COLUMN</code> is noise and can be omitted.206 </p><p>207 Consistency with the foreign server is not checked when a column is added208 or removed with <code class="literal">ADD COLUMN</code> or209 <code class="literal">DROP COLUMN</code>, a <code class="literal">NOT NULL</code>210 or <code class="literal">CHECK</code> constraint is added, or a column type is changed211 with <code class="literal">SET DATA TYPE</code>. It is the user's responsibility to ensure212 that the table definition matches the remote side.213 </p><p>214 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 valid215 parameters.216 </p></div><div class="refsect1" id="id-1.9.3.13.8"><h2>Examples</h2><p>217 To mark a column as not-null:218</p><pre class="programlisting">219ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;220</pre><p>221 </p><p>222 To change options of a foreign table:223</p><pre class="programlisting">224ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3);225</pre></div><div class="refsect1" id="id-1.9.3.13.9"><h2>Compatibility</h2><p>226 The forms <code class="literal">ADD</code>, <code class="literal">DROP</code>,227 and <code class="literal">SET DATA TYPE</code>228 conform with the SQL standard. The other forms are229 <span class="productname">PostgreSQL</span> extensions of the SQL standard.230 Also, the ability to specify more than one manipulation in a single231 <code class="command">ALTER FOREIGN TABLE</code> command is an extension.232 </p><p>233 <code class="command">ALTER FOREIGN TABLE DROP COLUMN</code> can be used to drop the only234 column of a foreign table, leaving a zero-column table. This is an235 extension of SQL, which disallows zero-column foreign tables.236 </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER FUNCTION</td></tr></table></div></body></html>