Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-alterforeigntable.html236 linesDownload Raw Back to html
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>
codekingpro/portable-devtools · Team Ai