MogDB
Ecological Tools
Doc Menu

CREATE FOREIGN TABLE

Function

CREATE FOREIGN TABLE creates a foreign table.

Precautions

The foreign table does not support the use of system columns (such as tableoid, ctid, etc.).

Syntax

CreateForeignTable ::= CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
    column_name type_name [ OPTIONS ( option 'value') ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    [, ... ]
] )
  SERVER server_name
[ OPTIONS ( option 'value' ) ]

The column_constraint can be:

column_constraint ::= [ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  DEFAULT default_expr }

Parameter Description

  • IF NOT EXISTS

    Sends a notice, but does not throw an error, if a table with the same name exists.

  • table_name

    Specifies the name of a foreign table.

    Value range: a string. It must comply with the naming convention.

  • column_name

    Specifies the name of a column in the foreign table.

    Value range: a string. It must comply with the naming convention.

  • type_name

    Specifies the data type of the column.

  • SERVER server_name

    Specifies the server name of the foreign table. The default value is mot_server.

  • OPTIONS ( option 'value' [, … ] )

    Options are related to the new foreign table or the columns in the foreign table. The allowed option names and values are specified by each foreign data wrapper and verified with the verification function of the foreign data wrapper. The option name must be unique (although table options and table column options can share the same name).

    • Options supported by oracle_fdw are as follows:

      • table

        Name of a table on the Oracle server. The value must be the same as the table name recorded in the Oracle system catalog. Generally, the value consists of uppercase letters.

      • schema

        Schema (or owner) corresponding to the table. The value must be the same as the table name recorded in the Oracle system catalog. Generally, the value consists of uppercase letters.

    • Options supported by mysql_fdw are as follows:

      • dbname

        Name of the MySQL database.

      • table_name

        Name of a table in the MySQL database.

    • Options supported by postgres_fdw are as follows:

      • schema_name

        Schema name of a remote server. If this option is not specified, the schema name of the foreign table is used as the schema name of the remote server.

      • table_name

        Table name of a remote server. If this option is not specified, the name of the foreign table is used as the table name of the remote server.

      • column_name

        Column name of a table on a remote server. If this option is not specified, the column name of the foreign table is used as the column name of a table on a remote server.