Use CREATE FOREIGN TABLE to define a foreign table - PolarDB

CREATE FOREIGN TABLE defines a new foreign table in the current database. The user who creates the table becomes its owner.

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, make sure you have:

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

Usage notes

If a schema name is specified, the table is created in that schema. For example: CREATE FOREIGN TABLE myschema.mytable .... Otherwise, the table is created in the current schema.
The name of the foreign table must be distinct from the names of other tables, sequences, indexes, views, materialized views, and foreign tables in the same schema.
CREATE FOREIGN TABLE automatically creates a data type representing the composite type of a row of the foreign table. A foreign table therefore cannot have the same name as an existing data type in the same schema.
If a PARTITION OF clause is specified, the table is created as a partition of the parent table with the specified bounds.

Parameters

Parameter	Description
`IF NOT EXISTS`	Suppresses an error if a relation with the same name already exists — issues a notice instead. The existing relation might not match the one you intended to create.
`table_name`	The name of the foreign table to create. Optionally schema-qualified.
`column_name`	The name of a column in the new table.
`data_type`	The data type of the column. Valid data types include 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 a list of parent tables from which the 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 the parent table with the specified bounds.
`CONSTRAINT constraint_name`	Optional. A name for a column or table constraint. If the constraint is violated, the name appears in the error message. Constraint names that contain spaces must be enclosed in double quotation marks (`"`). If omitted, the system generates a name automatically.
`NOT NULL`	Prevents the column from containing NULL values.
`NULL`	Allows the column to contain NULL values. This is the default.
`CHECK ( expression ) [ NO INHERIT ]`	Specifies an expression that produces a Boolean result. Each row must satisfy the expression — the result must be TRUE or UNKNOWN, not FALSE. A column constraint expression must reference only that column's value; a table constraint expression can reference multiple columns. The expression cannot contain subqueries or reference variables other than columns of the current row. It may reference the `tableoid` system column, but not other system columns. Constraints marked `NO INHERIT` are not propagated to child tables. Note This clause exists only for compatibility with non-standard SQL databases and is not recommended.
`DEFAULT default_expr`	Assigns a default value to the column. The expression cannot contain variables, subqueries, or cross-references to other columns in the table. The data type of the expression must match the column's data type. The default is used in insert operations that do not supply a value for the column. If no default is defined, the default is NULL.
`GENERATED ALWAYS AS ( generation_expr ) STORED`	Creates a generated column. The column is not writable; it returns the value of the specified expression when read. `STORED` is required and indicates that the value is computed when rows are written. The computed value is passed to the foreign data wrapper for storage and returned when the column is read. The expression can reference other columns in the table but not other generated columns. Only immutable functions and operators are allowed, and the expression cannot reference other tables.
`server_name`	The name of an existing foreign server to associate with the foreign table.
`OPTIONS ( option 'value' [, ... ] )`	Options to associate with the new foreign table or one of its columns. Allowed option names and values depend on the foreign data wrapper and are validated by its validator function. Duplicate option names are not allowed, except that a table option and a column option may share the same name.

Example

The following example creates a foreign table that maps to a table in a remote database through a foreign 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 creating the foreign table, query and manage its data the same way as a regular table.