CREATE FOREIGN TABLE - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to define a new foreign table by using CREATE FOREIGN 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' [, ... ] ) ]

Note

Details of column_constraint:

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

Details of table_constraint:

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

Usage notes

CREATE FOREIGN TABLE is used to create a new foreign table in the current database. The user who creates the table becomes the owner of the table.
If a schema name is specified, the table is created in the specified schema. 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 the tables, sequences, indexes, views, materialized views, and other foreign tables in the same schema.
CREATE FOREIGN TABLE also automatically creates a data type to represent the composite type of a row of the foreign table. Therefore, a foreign table 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 parent_table that has the specified bounds.
If you need to create a foreign table, you must have the USAGE privilege on the foreign server and the USAGE privilege on all column types used in the table.

Components

Component	Description
IF NOT EXISTS	Do not throw an error if a relation with the same name already exists. Issue a notice instead. Note The existing relation might not be exactly the same as the one that you want to establish.
table_name	The name of the table to be created. You can specify the schema of the table.
column_name	The name of the column to be created in the new table.
data_type	The data type of the column. Valid data types include array specifiers.
COLLATE collation	The COLLATE clause assigns a collation to the column, which must be of a sortable data type. If this clause is not specified, the default collation for the data type of the column is used.
INHERITS ( parent_table[, ... ] )	Optional. The INHERITS clause specifies a list of parent tables from which the new foreign table automatically inherits all columns. The parent tables can be regular tables or foreign tables.
PARTITION OF parent_tableFOR VALUES partition_bound_spec	The PARTITION OF clause. If this clause is specified, the table is created as a partition of the parent table with the specified bounds.
CONSTRAINT constraint_name	Optional. The name for a column or table constraint. If the constraint is violated, the constraint name appears in the error message. You can use constraint names such as `col must be positive` to communicate useful constraint information to client applications. If a constraint name contains spaces, you must place the name in double quotation marks (" "). If you do not specify a constraint name, the system automatically generates one.
NOT NULL	The column cannot contain null values.
NULL	The column can contain null values. By default, null values are used.
CHECK ( expression) [ NO INHERIT ]	The CHECK clause specifies an expression that produces a Boolean result. Each row in the foreign table must satisfy the expression. For all rows in the foreign table, this expression must produce TRUE or UNKNOWN but not FALSE. A check constraint that is specified as a column constraint must reference only the value of the corresponding column, whereas an expression that appears in a table constraint can reference multiple columns. A CHECK expression cannot contain subqueries or reference variables other than the columns of the current row. The expression can reference the `tableoid` system column, but not other system columns. Constraints marked with NO INHERIT will not be propagated to child tables. Note This clause is only for compatibility with non-standard SQL databases and is not recommended.
DEFAULT default_expr	The DEFAULT clause assigns a default data value to the column whose definition contains the clause. The value is an expression that does not contain variables. The expression cannot contain subqueries or make cross-references to other columns in the current table. The data type of the expression must match the data type of the column. The expression is used in insert operations that do not specify a value for the column. If a column does not have a default value, the default value is null.
GENERATED ALWAYS AS ( generation_expr) STORED	This clause creates a generated column. This column is not writable and returns the value of the specified expression when the column is read. The STORED keyword is required to indicate that the column value will be evaluated when rows are written. The value will be passed to the foreign data wrapper for storage and returned when the column is read. The generation expression can reference other columns in the table, but not other generated columns. The generation expression can use only immutable functions and operators and cannot reference other tables.
server_name	The name of an existing foreign server for the foreign table.
OPTIONS ( option'value' [, ...] )	The options to be associated with the new foreign table or one of its columns. The allowed option names and values depend on the foreign data wrapper, and are validated by using the validator function of the foreign data wrapper. You cannot use duplicate option names except that you use the same name for a table option and a column option.

Example

The following sample code shows how to create the foreign table films and access the table by using the server film_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.