diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createforeigntable.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createforeigntable.html | 244 |
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..1572f71 --- /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 15.5 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 15.5 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 |