CREATE FOREIGN TABLE-PolarDB(PolarDB)-阿里云帮助中心

CREATE FOREIGN TABLE defines a new foreign table in the current database, letting you query data on a remote server as if it were a local table.

Syntax

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
  { column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name
  PARTITION OF parent_table [ (
  { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ] partition_bound_spec
  SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

Where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  GENERATED ALWAYS AS ( generation_expr ) STORED }

And table_constraint is:

[ CONSTRAINT constraint_name ]
CHECK ( expression ) [ NO INHERIT ]

Prerequisites

Before you begin, ensure that you have:

USAGE privilege on the foreign server
USAGE privilege on all column types used in the table

Usage notes

The user who runs CREATE FOREIGN TABLE becomes the owner of the table.
If a schema name is specified (for example, CREATE FOREIGN TABLE myschema.mytable ...), the table is created in that schema. Otherwise, it is created in the current schema.
The foreign table name must be distinct from the names of all other tables, sequences, indexes, views, materialized views, and foreign tables in the same schema.
CREATE FOREIGN TABLE automatically creates a composite type to represent a row of the foreign table. The foreign table therefore cannot share a name with any existing data type in the same schema.
If PARTITION OF is specified, the table is created as a partition of parent_table with the specified bounds.

Parameters

Parameter	Description
`IF NOT EXISTS`	Issues a notice instead of an error when a relation with the same name already exists. The existing relation may not match the one you intend to create.
`table_name`	The name of the foreign table to create. Include the schema name to create the table in a specific schema.
`column_name`	The name of a column in the new table.
`data_type`	The data type of the column, including array specifiers.
`COLLATE collation`	Assigns a collation to the column. The column must be of a sortable data type. If omitted, the default collation for the column's data type is used.
`INHERITS ( parent_table [, ...] )`	Optional. Specifies parent tables from which the new foreign table inherits all columns. Parent tables can be regular tables or foreign tables.
`PARTITION OF parent_table FOR VALUES partition_bound_spec`	Creates the table as a partition of `parent_table` with the specified bounds.
`CONSTRAINT constraint_name`	Optional. A name for a column or table constraint. If the constraint is violated, this name appears in the error message. Names that contain spaces must be enclosed in double quotation marks (`"`). If omitted, the system generates a name automatically.
`NOT NULL`	The column cannot contain null values.
`NULL`	The column can contain null values. This is the default.
`CHECK ( expression ) [ NO INHERIT ]`	An expression that must produce TRUE or UNKNOWN (never FALSE) for every row. A column constraint can reference only that column's value; a table constraint can reference multiple columns. The expression cannot contain subqueries or reference variables other than the current row's columns, except for the `tableoid` system column. Constraints marked `NO INHERIT` are not propagated to child tables. Note This clause is only for compatibility with non-standard SQL databases and is not recommended.
`DEFAULT default_expr`	A default value for the column. The expression cannot contain variables, subqueries, or cross-references to other columns. Its data type must match the column's data type. If no default is specified, the default is null.
`GENERATED ALWAYS AS ( generation_expr ) STORED`	Creates a generated column whose value is computed from `generation_expr` whenever the row is written. The column is read-only. The value is passed to the foreign data wrapper for storage and returned when the column is read. The `STORED` keyword is required. The expression can reference other (non-generated) columns in the table, but not other generated columns, other tables, or non-immutable functions and operators.
`server_name`	The name of an existing foreign server to associate with the foreign table.
`OPTIONS ( option 'value' [, ...] )`	Options for the foreign table or a column. Allowed names and values depend on the foreign data wrapper and are validated by its validator function. Duplicate option names are not allowed, except when the same name is used for both a table option and a column option.

Example

The following example creates the foreign table foreign_table and accesses it by using the server foreign_server. The foreign table maps to a table named some_table in the some_schema schema on a remote server.

CREATE FOREIGN TABLE foreign_table (
  id    integer NOT NULL,
  data  text
)
SERVER foreign_server
OPTIONS (schema_name 'some_schema', table_name 'some_table');

After you create the foreign table, you can query and manage data in the foreign table in the same way as in a regular table.

What's next

ALTER FOREIGN TABLE: Modify the definition of a foreign table.
DROP FOREIGN TABLE: Remove a foreign table.
IMPORT FOREIGN SCHEMA: Automatically create foreign tables for all or a subset of tables in a remote schema — a faster alternative to writing CREATE FOREIGN TABLE statements individually when the remote schema has many tables.