CREATE FOREIGN TABLE
CREATE FOREIGN TABLE - Define a new foreign table
Summary
CREATE FOREIGN TABLE [IF NOT FOUND]tabelnavn( [ {kolonnenavndata type[ OPPORTUNITIES (Capability'Wert' [, ... ] ) ] [ COLLECTORcollection] [constraint column[ ... ] ] |Constraint_Array} [, ... ]] )[ INherits (parent table[, ... ] ) ] SERVERServername[ OPPORTUNITIES (Capability'Wert' [, ... ] ) ]CREATE FOREIGN TABLE [IF NOT FOUND]tabelnavnCOMMUNICATION OFparent table[ ( {kolonnenavn[ WITH OPTIONS ] [constraint column[ ... ] ] |Constraint_Array} [, ... ]) ]{ FOR VALUESpartition_bound_spec| STANDARD} SERVERServername[ OPPORTUNITIES (Capability'Wert[, ... ] ) ]Woconstraint columnIs:[ RESTRICTIONconstraint name]{ NO MACHINE | NULL | CONTROL (Expression) [NO INHERITANCE] | EXAMPLEdefault_expr| IT ALWAYS HAPPENS LIKE (generation_strange) RESCUED }AndConstraint_ArrayIs:[ RESTRICTIONconstraint name]CHECK OVER (Expression) [NO LEGACY]Andpartition_bound_specIs:IN (partition_bound_expr[, ...] ) |DISABLE ( {partition_bound_expr| MINDESTWERT | MAXVALUE } [, ...] ) TO ( {partition_bound_expr| MINIMUM | MAXVALUE } [, ...] ) |WITH ( SECTIONarithmetisches_Literal, THE RESTarithmetisches_Literal)
Description
CREATE FOREIGN TABLECreates a new foreign table in the current database. The table is owned by the user issuing the command.
If a schema name is specified (e.gCREATE A FOREIGN TABLE myschema.mytable ...), then the table is created in the specified form. Otherwise it will be created in the current program. The foreign table name must be different from the name of another relationship (array, sequence, index, view, materialized view, or foreign table) in the same schema.
CREATE FOREIGN TABLEIt also automatically creates a data type that represents the complex type that corresponds to a row in the foreign table. Therefore, foreign tables cannot have the same name as an existing data type in the same schema.
IfCOMMUNICATION OFThe condition is specified and then the array is created as a partitionparent tablewith defined boundaries.
To be able to create a foreign table, you must have the followingUSEPrivilege also on the foreign serverUSEPermission for all column types used in the table.
Parameter
IF IT DOES NOT EXIST-
Make no mistake if a relationship with the same name already exists. A notice will be issued about this. Note that there is no guarantee that the existing relationship will be similar to the one that would have been created.
tabelnavn-
The name (optionally schema-compliant) of the array to create.
kolonnenavn-
The name of a column to create in the new table.
data type-
The data type of the column. This can include array specifications. Learn more about the data types supported byPostgreSQL, to which I referChapter 8.
COLLECTORcollection-
The
COLLECTORThe condition assigns a collation (which must have a sortable data type) to the column. If not specified, the default collation of the column data type is used. INHERIT (parent table[,...])-
The optional
HE INheritsThe clause specifies a list of tables from which the new foreign table automatically inherits all columns. Parent tables can be regular or foreign tables. Look at the similar shapeCREATE A TABLEFor more informations. COMMUNICATION OFparent table{ FOR VALUESpartition_bound_spec| EXAMPLE }-
This form can be used to create the foreign array as a partition of the specified parent array with specified partition limits. Look at the similar shapeCREATE A TABLEFor more informations. Note that it is currently not allowed to create the foreign table as a partition of the parent table if it exists
UNIQUEIndexes on the parent table. (See alsoCHANGE TABLE, ADD SEPARATOR.) RESTRICTIONconstraint name-
An optional name for a column or table constraint. If the constraint is violated, the constraint name is present in the error messages, so constraint names such as
col must be positivecan be used to pass useful throttling information to client applications. (Double quotes are required to specify constraint names that contain spaces.) If no constraint name is specified, the system creates one. NOT EMPTY-
The column cannot contain null values.
NULL-
The column must contain null values. This is the default.
This clause is for compatibility with non-standard SQL databases only. It is not recommended to use it in new applications.
CONTROL (Expression) [NO LEGACY]-
The
CONTROLThe condition specifies an expression that produces a boolean result that should match each row in the foreign array. That is, the expression must evaluate to TRUE or UNKNOWN, never FALSE, for all rows in the foreign table. A control constraint specified as a column constraint should refer only to the value of that column, while an expression appearing in a table constraint can refer to multiple columns.At the moment,
CONTROLExpressions cannot contain subqueries or refer to variables other than columns in the current row. The system columnDesktopcan be referenced, but not another system column.A restriction marked with
NO LEGACYit is not transferred to children's tables. EXAMPLEdefault_expr-
The
EXAMPLEThe condition assigns a default data value to the column whose column definition is displayed. The value is any non-variable expression (subqueries and references to other columns in the current table are not allowed). The data type of the regular expression must match the data type of the column.The default expression is used in any input operation that does not specify a value for the column. If a column does not have a default value, the default value is null.
IT ALWAYS HAPPENS LIKE (generation_strange) HOLD-
This clause creates the column ascolumn created. The column cannot be written to and reading it returns the result of the specified expression.
The keyword
HOLDis required to indicate that the column is computed on write. (The calculated value is displayed for storage in the foreign data wrapper and must be returned when read.)The creation expression can reference other columns in the table, but not other created columns. All functions and operators used must be immutable. References to other tables are not permitted.
Servername-
The name of an existing remote server to use for the remote table. For server definition details, seeCREATE SERVERS.
OPPORTUNITIES (Capability'Wert[, ...] )-
Options for appending to the new foreign table or one of its columns. Allowed option names and values are specific to each foreign data wrapper and are validated using the foreign data wrapper's validation function. Duplicate setting names are not allowed (although it's okay for a table setting and a column setting to have the same name).
Observe
Restrictions on foreign tables (e.gCONTROLDieNOT EMPTYclauses) are not enforced by the kernelPostgreSQLsystem, and most foreign data wrappers don't try to enforce them. that is, the constraint is simply assumed to hold. Such enforcement would not make sense since it would only apply to rows that are inserted or updated via the foreign table and not to rows that have been modified in some other way, for example directly on the remote server. Instead, a constraint bound to a foreign table should represent a constraint enforced by the remote server.
Some special foreign data wrappers may provide the only access mechanism for the data they access. Therefore, it might make sense for the foreign data wrapper to do the constraint enforcement itself. But don't assume a wrapper will do this unless the documentation says so.
AlthoughPostgreSQLdoes not attempt to impose restrictions on foreign tables, but assumes they are correct for query optimization purposes. If there are visible rows in the foreign table that don't satisfy a declared constraint, queries on the table can return errors or wrong answers. It is the user's responsibility to ensure that the constraint definition corresponds to reality.
warning
When a foreign array is used as a partition of a partitioned array, there is an implicit constraint that its contents must satisfy the partition rule. Again, it is the user's responsibility to ensure this is the case. This is best done by installing an appropriate restriction on the remote server.
Within a partition table that contains partitions from foreign tables, aMODERNIZEThis will change the value of the partition key. This can result in a row being moved from a local partition to a foreign table partition, provided the foreign data wrapper supports tuple routing. However, it is currently not possible to move a row from one partition of a foreign table to another partition. OneMODERNIZEHowever, if it is required then this will fail due to the partition constraint as long as it is properly enforced by the remote server.
Similar considerations apply to the columns to be created. Saved generated columns are calculated when inserting or updating the local onesPostgreSQLServer and is passed to the foreign data wrapper to print to the foreign data store, but does not force a query of the foreign table to return values for created stored columns that match the creation expression. This can also lead to incorrect query results.
examples
Create a foreign tableFilm, which you access via the serverfilm_server:
CREATE FOREIGN TABLE movies (char(5) NOT NULL Code, varchar(40) NOT NULL Title, NOT NULL Integer, Production Date, varchar(10) Genre, Lens Interval in Minuten) SERVER movie_server;
Create a foreign tableMeasurement_y2016m07, which you access via the serverserver_07, as a partition of the array with range partitionsMeasurement:
CREATE FOREIGN TABLE medium_y2016m07 DIVIDE BY COUNT FOR VALUES FROM ('2016-07-01') TO ('2016-08-01') SERVER server_07; compatibility
TheCREATE FOREIGN TABLEThe command is largely the same asSQLExample; like meCREATE A TABLE,NULLConstraints and foreign arrays with null columns are allowed. The option to set default column values is also presentPostgreSQLRenewal. Array inheritance in the form defined byPostgreSQL, is not default.
See also
CHANGE THE OUTDOOR TABLE,DROP AN FOREIGN TABLE,CREATE A TABLE,CREATE SERVERS,IMPORT FOREIGN FORM