Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 13:44:03 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-13 13:44:03 +0000
commit: 293913568e6a7a86fd1479e1cff8e2ecb58d6568 (patch)
tree: fc3b469a3ec5ab71b36ea97cc7aaddb838423a0c /doc/src/sgml/html/sql-createforeigntable.html
parent: Initial commit. (diff)
download: postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.tar.xz
postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.zip
1 files changed, 244 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createforeigntable.html b/doc/src/sgml/html/sql-createforeigntable.html
new file mode 100644
index 0000000..8fc6a1e
--- /dev/null
+++ b/doc/src/sgml/html/sql-createforeigntable.html
@@ -0,0 +1,244 @@
+<?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>CREATE 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-createforeigndatawrapper.html" title="CREATE FOREIGN DATA WRAPPER" /><link rel="next" href="sql-createfunction.html" title="CREATE 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">CREATE FOREIGN TABLE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createforeigndatawrapper.html" title="CREATE 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-createfunction.html" title="CREATE FUNCTION">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEFOREIGNTABLE"><div class="titlepage"></div><a id="id-1.9.3.66.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE FOREIGN TABLE</span></h2><p>CREATE FOREIGN TABLE — define a new foreign table</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
+CREATE FOREIGN TABLE [ IF NOT EXISTS ] <em class="replaceable"><code>table_name</code></em> ( [
+  { <em class="replaceable"><code>column_name</code></em> <em class="replaceable"><code>data_type</code></em> [ OPTIONS ( <em class="replaceable"><code>option</code></em> '<em class="replaceable"><code>value</code></em>' [, ... ] ) ] [ COLLATE <em class="replaceable"><code>collation</code></em> ] [ <em class="replaceable"><code>column_constraint</code></em> [ ... ] ]
+    | <em class="replaceable"><code>table_constraint</code></em> }
+    [, ... ]
+] )
+[ INHERITS ( <em class="replaceable"><code>parent_table</code></em> [, ... ] ) ]
+  SERVER <em class="replaceable"><code>server_name</code></em>
+[ OPTIONS ( <em class="replaceable"><code>option</code></em> '<em class="replaceable"><code>value</code></em>' [, ... ] ) ]
+
+CREATE FOREIGN TABLE [ IF NOT EXISTS ] <em class="replaceable"><code>table_name</code></em>
+  PARTITION OF <em class="replaceable"><code>parent_table</code></em> [ (
+  { <em class="replaceable"><code>column_name</code></em> [ WITH OPTIONS ] [ <em class="replaceable"><code>column_constraint</code></em> [ ... ] ]
+    | <em class="replaceable"><code>table_constraint</code></em> }
+    [, ... ]
+) ]
+{ FOR VALUES <em class="replaceable"><code>partition_bound_spec</code></em> | DEFAULT }
+  SERVER <em class="replaceable"><code>server_name</code></em>
+[ OPTIONS ( <em class="replaceable"><code>option</code></em> '<em class="replaceable"><code>value</code></em>' [, ... ] ) ]
+
+<span class="phrase">where <em class="replaceable"><code>column_constraint</code></em> is:</span>
+
+[ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]
+{ NOT NULL |
+  NULL |
+  CHECK ( <em class="replaceable"><code>expression</code></em> ) [ NO INHERIT ] |
+  DEFAULT <em class="replaceable"><code>default_expr</code></em> |
+  GENERATED ALWAYS AS ( <em class="replaceable"><code>generation_expr</code></em> ) STORED }
+
+<span class="phrase">and <em class="replaceable"><code>table_constraint</code></em> is:</span>
+
+[ CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> ]
+CHECK ( <em class="replaceable"><code>expression</code></em> ) [ NO INHERIT ]
+
+<span class="phrase">and <em class="replaceable"><code>partition_bound_spec</code></em> is:</span>
+
+IN ( <em class="replaceable"><code>partition_bound_expr</code></em> [, ...] ) |
+FROM ( { <em class="replaceable"><code>partition_bound_expr</code></em> | MINVALUE | MAXVALUE } [, ...] )
+  TO ( { <em class="replaceable"><code>partition_bound_expr</code></em> | MINVALUE | MAXVALUE } [, ...] ) |
+WITH ( MODULUS <em class="replaceable"><code>numeric_literal</code></em>, REMAINDER <em class="replaceable"><code>numeric_literal</code></em> )
+</pre></div><div class="refsect1" id="SQL-CREATEFOREIGNTABLE-DESCRIPTION"><h2>Description</h2><p>
+   <code class="command">CREATE FOREIGN TABLE</code> creates a new foreign table
+   in the current database. The table will be owned by the user issuing the
+   command.
+  </p><p>
+   If a schema name is given (for example, <code class="literal">CREATE FOREIGN TABLE
+   myschema.mytable ...</code>) then the table is created in the specified
+   schema.  Otherwise it is created in the current schema.
+   The name of the foreign table must be
+   distinct from the name of any other relation (table, sequence, index, view,
+   materialized view, or foreign table) in the same schema.
+  </p><p>
+   <code class="command">CREATE FOREIGN TABLE</code> also automatically creates a data
+   type that represents the composite type corresponding to one row of
+   the foreign table.  Therefore, foreign tables cannot have the same
+   name as any existing data type in the same schema.
+  </p><p>
+   If <code class="literal">PARTITION OF</code> clause is specified then the table is
+   created as a partition of <code class="literal">parent_table</code> with specified
+   bounds.
+  </p><p>
+   To be able to create a foreign table, you must have <code class="literal">USAGE</code>
+   privilege on the foreign server, as well as <code class="literal">USAGE</code>
+   privilege on all column types used in the table.
+  </p></div><div class="refsect1" id="id-1.9.3.66.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">IF NOT EXISTS</code></span></dt><dd><p>
+      Do not throw an error if a relation with the same name already exists.
+      A notice is issued in this case.  Note that there is no guarantee that
+      the existing relation is anything like the one that would have been
+      created.
+     </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>
+      The name (optionally schema-qualified) of the table to be created.
+     </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>
+      The name of a column to be created in the new table.
+     </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p>
+      The data type of the column. This can include array
+      specifiers. For more information on the data types supported by
+      <span class="productname">PostgreSQL</span>, refer to <a class="xref" href="datatype.html" title="Chapter 8. Data Types">Chapter 8</a>.
+     </p></dd><dt><span class="term"><code class="literal">COLLATE <em class="replaceable"><code>collation</code></em></code></span></dt><dd><p>
+      The <code class="literal">COLLATE</code> clause assigns a collation to
+      the column (which must be of a collatable data type).
+      If not specified, the column data type's default collation is used.
+     </p></dd><dt><span class="term"><code class="literal">INHERITS ( <em class="replaceable"><code>parent_table</code></em> [, ... ] )</code></span></dt><dd><p>
+      The optional <code class="literal">INHERITS</code> clause specifies a list of
+      tables from which the new foreign table automatically inherits
+      all columns.  Parent tables can be plain tables or foreign tables.
+      See the similar form of
+      <a class="link" href="sql-createtable.html" title="CREATE TABLE"><code class="command">CREATE TABLE</code></a> for more details.
+     </p></dd><dt><span class="term"><code class="literal">PARTITION OF <em class="replaceable"><code>parent_table</code></em> { FOR VALUES <em class="replaceable"><code>partition_bound_spec</code></em> | DEFAULT }</code></span></dt><dd><p>
+      This form can be used to create the foreign table as partition of
+      the given parent table with specified partition bound values.
+      See the similar form of
+      <a class="link" href="sql-createtable.html" title="CREATE TABLE"><code class="command">CREATE TABLE</code></a> for more details.
+      Note that it is currently not allowed to create the foreign table as a
+      partition of the parent table if there are <code class="literal">UNIQUE</code>
+      indexes on the parent table.  (See also
+      <a class="link" href="sql-altertable.html" title="ALTER TABLE"><code class="command">ALTER TABLE ATTACH PARTITION</code></a>.)
+     </p></dd><dt><span class="term"><code class="literal">CONSTRAINT <em class="replaceable"><code>constraint_name</code></em></code></span></dt><dd><p>
+      An optional name for a column or table constraint.  If the
+      constraint is violated, the constraint name is present in error messages,
+      so constraint names like <code class="literal">col must be positive</code> can be used
+      to communicate helpful constraint information to client applications.
+      (Double-quotes are needed to specify constraint names that contain spaces.)
+      If a constraint name is not specified, the system generates a name.
+     </p></dd><dt><span class="term"><code class="literal">NOT NULL</code></span></dt><dd><p>
+      The column is not allowed to contain null values.
+     </p></dd><dt><span class="term"><code class="literal">NULL</code></span></dt><dd><p>
+      The column is allowed to contain null values. This is the default.
+     </p><p>
+      This clause is only provided for compatibility with
+      non-standard SQL databases.  Its use is discouraged in new
+      applications.
+     </p></dd><dt><span class="term"><code class="literal">CHECK ( <em class="replaceable"><code>expression</code></em> ) [ NO INHERIT ] </code></span></dt><dd><p>
+      The <code class="literal">CHECK</code> clause specifies an expression producing a
+      Boolean result which each row in the foreign table is expected
+      to satisfy; that is, the expression should produce TRUE or UNKNOWN,
+      never FALSE, for all rows in the foreign table.
+      A check constraint specified as a column constraint should
+      reference that column's value only, while an expression
+      appearing in a table constraint can reference multiple columns.
+     </p><p>
+      Currently, <code class="literal">CHECK</code> expressions cannot contain
+      subqueries nor refer to variables other than columns of the
+      current row.  The system column <code class="literal">tableoid</code>
+      may be referenced, but not any other system column.
+     </p><p>
+      A constraint marked with <code class="literal">NO INHERIT</code> will not propagate to
+      child tables.
+     </p></dd><dt><span class="term"><code class="literal">DEFAULT
+    <em class="replaceable"><code>default_expr</code></em></code></span></dt><dd><p>
+      The <code class="literal">DEFAULT</code> clause assigns a default data value for
+      the column whose column definition it appears within.  The value
+      is any variable-free expression (subqueries and cross-references
+      to other columns in the current table are not allowed).  The
+      data type of the default expression must match the data type of the
+      column.
+     </p><p>
+      The default expression will be used in any insert operation that
+      does not specify a value for the column.  If there is no default
+      for a column, then the default is null.
+     </p></dd><dt><span class="term"><code class="literal">GENERATED ALWAYS AS ( <em class="replaceable"><code>generation_expr</code></em> ) STORED</code><a id="id-1.9.3.66.6.2.13.1.2" class="indexterm"></a></span></dt><dd><p>
+      This clause creates the column as a <em class="firstterm">generated
+      column</em>.  The column cannot be written to, and when read the
+      result of the specified expression will be returned.
+     </p><p>
+      The keyword <code class="literal">STORED</code> is required to signify that the
+      column will be computed on write.  (The computed value will be presented
+      to the foreign-data wrapper for storage and must be returned on
+      reading.)
+     </p><p>
+      The generation expression can refer to other columns in the table, but
+      not other generated columns.  Any functions and operators used must be
+      immutable.  References to other tables are not allowed.
+     </p></dd><dt><span class="term"><em class="replaceable"><code>server_name</code></em></span></dt><dd><p>
+      The name of an existing foreign server to use for the foreign table.
+      For details on defining a server, see <a class="xref" href="sql-createserver.html" title="CREATE SERVER"><span class="refentrytitle">CREATE SERVER</span></a>.
+     </p></dd><dt><span class="term"><code class="literal">OPTIONS ( <em class="replaceable"><code>option</code></em> '<em class="replaceable"><code>value</code></em>' [, ...] )</code></span></dt><dd><p>
+      Options to be associated with the new foreign table or one of its
+      columns.
+      The allowed option names and values are specific to each foreign
+      data wrapper and are validated using the foreign-data wrapper's
+      validator function.  Duplicate option names are not allowed (although
+      it's OK for a table option and a column option to have the same name).
+     </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.66.7"><h2>Notes</h2><p>
+    Constraints on foreign tables (such as <code class="literal">CHECK</code>
+    or <code class="literal">NOT NULL</code> clauses) are not enforced by the
+    core <span class="productname">PostgreSQL</span> system, and most foreign data wrappers
+    do not attempt to enforce them either; that is, the constraint is
+    simply assumed to hold true.  There would be little point in such
+    enforcement since it would only apply to rows inserted or updated via
+    the foreign table, and not to rows modified by other means, such as
+    directly on the remote server.  Instead, a constraint attached to a
+    foreign table should represent a constraint that is being enforced by
+    the remote server.
+   </p><p>
+    Some special-purpose foreign data wrappers might be the only access
+    mechanism for the data they access, and in that case it might be
+    appropriate for the foreign data wrapper itself to perform constraint
+    enforcement.  But you should not assume that a wrapper does that
+    unless its documentation says so.
+   </p><p>
+    Although <span class="productname">PostgreSQL</span> does not attempt to enforce
+    constraints on foreign tables, it does assume that they are correct
+    for purposes of query optimization.  If there are rows visible in the
+    foreign table that do not satisfy a declared constraint, queries on
+    the table might produce errors or incorrect answers.  It is the user's
+    responsibility to ensure that the constraint definition matches
+    reality.
+   </p><div class="caution"><h3 class="title">Caution</h3><p>
+     When a foreign table is used as a partition of a partitioned table,
+     there is an implicit constraint that its contents must satisfy the
+     partitioning rule.  Again, it is the user's responsibility to ensure
+     that that is true, which is best done by installing a matching
+     constraint on the remote server.
+    </p></div><p>
+    Within a partitioned table containing foreign-table partitions,
+    an <code class="command">UPDATE</code> that changes the partition key value can
+    cause a row to be moved from a local partition to a foreign-table
+    partition, provided the foreign data wrapper supports tuple routing.
+    However, it is not currently possible to move a row from a
+    foreign-table partition to another partition.
+    An <code class="command">UPDATE</code> that would require doing that will fail
+    due to the partitioning constraint, assuming that that is properly
+    enforced by the remote server.
+   </p><p>
+    Similar considerations apply to generated columns.  Stored generated
+    columns are computed on insert or update on the local
+    <span class="productname">PostgreSQL</span> server and handed to the
+    foreign-data wrapper for writing out to the foreign data store, but it is
+    not enforced that a query of the foreign table returns values for stored
+    generated columns that are consistent with the generation expression.
+    Again, this might result in incorrect query results.
+   </p></div><div class="refsect1" id="SQL-CREATEFOREIGNTABLE-EXAMPLES"><h2>Examples</h2><p>
+   Create foreign table <code class="structname">films</code>, which will be accessed through
+   the server <code class="structname">film_server</code>:
+
+</p><pre class="programlisting">
+CREATE FOREIGN TABLE films (
+    code        char(5) NOT NULL,
+    title       varchar(40) NOT NULL,
+    did         integer NOT NULL,
+    date_prod   date,
+    kind        varchar(10),
+    len         interval hour to minute
+)
+SERVER film_server;
+</pre><p>
+   Create foreign table <code class="structname">measurement_y2016m07</code>, which will be
+   accessed through the server <code class="structname">server_07</code>, as a partition
+   of the range partitioned table <code class="structname">measurement</code>:
+
+</p><pre class="programlisting">
+CREATE FOREIGN TABLE measurement_y2016m07
+    PARTITION OF measurement FOR VALUES FROM ('2016-07-01') TO ('2016-08-01')
+    SERVER server_07;
+</pre></div><div class="refsect1" id="SQL-CREATEFOREIGNTABLE-COMPATIBILITY"><h2>Compatibility</h2><p>
+   The <code class="command">CREATE FOREIGN TABLE</code> command largely conforms to the
+   <acronym class="acronym">SQL</acronym> standard; however, much as with
+   <a class="link" href="sql-createtable.html" title="CREATE TABLE"><code class="command">CREATE TABLE</code></a>,
+   <code class="literal">NULL</code> constraints and zero-column foreign tables are permitted.
+   The ability to specify column default values is also
+   a <span class="productname">PostgreSQL</span> extension.  Table inheritance, in the form
+   defined by <span class="productname">PostgreSQL</span>, is nonstandard.
+  </p></div><div class="refsect1" id="id-1.9.3.66.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alterforeigntable.html" title="ALTER FOREIGN TABLE"><span class="refentrytitle">ALTER FOREIGN TABLE</span></a>, <a class="xref" href="sql-dropforeigntable.html" title="DROP FOREIGN TABLE"><span class="refentrytitle">DROP FOREIGN TABLE</span></a>, <a class="xref" href="sql-createtable.html" title="CREATE TABLE"><span class="refentrytitle">CREATE TABLE</span></a>, <a class="xref" href="sql-createserver.html" title="CREATE SERVER"><span class="refentrytitle">CREATE SERVER</span></a>, <a class="xref" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA"><span class="refentrytitle">IMPORT FOREIGN SCHEMA</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-createforeigndatawrapper.html" title="CREATE 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-createfunction.html" title="CREATE FUNCTION">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE 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"> CREATE FUNCTION</td></tr></table></div></body></html>
+\ No newline at end of file
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 13:44:03 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-13 13:44:03 +0000
commit	293913568e6a7a86fd1479e1cff8e2ecb58d6568 (patch)
tree	fc3b469a3ec5ab71b36ea97cc7aaddb838423a0c /doc/src/sgml/html/sql-createforeigntable.html
parent	Initial commit. (diff)
download	postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.tar.xz postgresql-16-293913568e6a7a86fd1479e1cff8e2ecb58d6568.zip