Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
Watch 1
Star 0
Wiki 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Branch: master
Branches Tags
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/sql-alterforeigntable.html

236 lines
21KB
Raw Permalink Blame History 

  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 V1.79.1" /><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 12.4 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">

  3. ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] <em class="replaceable"><code>name</code></em> [ * ]

  4.     <em class="replaceable"><code>action</code></em> [, ... ]

  5. ALTER 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>

  7. ALTER FOREIGN TABLE [ IF EXISTS ] <em class="replaceable"><code>name</code></em>

  8.     RENAME TO <em class="replaceable"><code>new_name</code></em>

  9. ALTER 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 DEFAULT

  19.     ALTER [ COLUMN ] <em class="replaceable"><code>column_name</code></em> { SET | DROP } NOT NULL

  20.     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 }

  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 OIDS

  33.     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_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 an

  39.    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 as

  43.       <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>.

  44.       Unlike the case when adding a column to a regular table, nothing happens

  45.       to the underlying storage: this action simply declares that

  46.       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> if

  50.       anything outside the table depends on the column; for example,

  51.       views.

  52.       If <code class="literal">IF EXISTS</code> is specified and the column

  53.       does not exist, no error is thrown. In this case a notice

  54.       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 simply

  58.       changes the type that <span class="productname">PostgreSQL</span> believes the column to

  59.       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 the

  64.       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 form

  69.       sets the per-column statistics-gathering target for subsequent

  70.       <a class="xref" href="sql-analyze.html" title="ANALYZE"><span class="refentrytitle">ANALYZE</span></a> operations.

  71.       See the similar form of <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></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="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></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="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></a>

  82.       for more details.

  83.       Note that the storage mode has no effect unless the table's

  84.       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 same

  87.       syntax as <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></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 is

  91.       done to verify the constraint is correct; rather, this action simply

  92.       declares that some new condition should be assumed to hold for all rows

  93.       in the foreign table.  (See the discussion

  94.       in <a class="xref" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE"><span class="refentrytitle">CREATE FOREIGN TABLE</span></a>.)

  95.       If the constraint is marked <code class="literal">NOT VALID</code>, then it isn't

  96.       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 marked

  99.       as <code class="literal">NOT VALID</code>.  No action is taken to verify the

  100.       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 constraint

  104.       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 foreign

  108.       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

  109.       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 added

  113.       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 specified

  116.       parent table.

  117.       See the similar form of <a class="xref" href="sql-altertable.html" title="ALTER TABLE"><span class="refentrytitle">ALTER TABLE</span></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 of

  121.       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 the

  124.       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 assumed

  129.       if no operation is explicitly specified.  Duplicate option names are not

  130.       allowed (although it's OK for a table option and a column option to have

  131.       the same name).  Option names and values are also validated using the

  132.       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 table

  135.       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 into

  142.    a list of multiple alterations to apply in parallel.  For example, it

  143.    is possible to add several columns and/or alter the type of several

  144.    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 is

  148.    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 have

  152.    <code class="literal">CREATE</code> privilege on the new schema.

  153.    To alter the owner, you must also be a direct or indirect member of the new

  154.    owning role, and that role must have <code class="literal">CREATE</code> privilege on

  155.    the table's schema.  (These restrictions enforce that altering the owner

  156.    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 also

  159.    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 to

  162.         alter. If <code class="literal">ONLY</code> is specified before the table name, only

  163.         that table is altered. If <code class="literal">ONLY</code> is not specified, the table

  164.         and all its descendant tables (if any) are altered.  Optionally,

  165.         <code class="literal">*</code> can be specified after the table name to explicitly

  166.         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 existing

  175.         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 column

  182.         or constraint (for example, views referencing the column),

  183.         and in turn all objects that depend on those objects

  184.         (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 dependent

  187.         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.  (This

  192.         requires superuser privilege if any of the triggers are internally

  193.         generated triggers.  The core system does not add such triggers to

  194.         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 except

  197.         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 added

  208.     or removed with <code class="literal">ADD COLUMN</code> or

  209.     <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 changed

  211.     with <code class="literal">SET DATA TYPE</code>.  It is the user's responsibility to ensure

  212.     that the table definition matches the remote side.

  213.    </p><p>

  214.     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

  215.     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">

  219. ALTER 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">

  224. ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3 'value3');

  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 are

  229.    <span class="productname">PostgreSQL</span> extensions of the SQL standard.

  230.    Also, the ability to specify more than one manipulation in a single

  231.    <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 only

  234.    column of a foreign table, leaving a zero-column table.  This is an

  235.    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">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>
上海开阖软件有限公司 沪ICP备12045867号-1