Updated on 2025-05-29 GMT+08:00

CREATE FOREIGN TABLE

Description

Creates a foreign table.

Precautions

  • System columns (such as tableoid and ctid) cannot be used in foreign tables. Foreign tables in the Private or Shared schema require the initial user permission or the O&M administrator permission in O&M mode (operation_mode).
  • When multi-layer quotation marks are used for sensitive columns (such as password and secret_access_key) in OPTIONS, the semantics is different from that in the scenario where quotation marks are not used. Therefore, sensitive columns are not identified for anonymization.
  • In the multi-tenancy scenario, only the initial user can run the CREATE FOREIGN TABLE command in the PDB.

Syntax

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' } [, ... ] ) ];
column_constraint is as follows:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  DEFAULT default_expr }

Parameters

  • 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.

  • COLLATE collation

    Assigns a collation to the column (which must be of a collatable data type). If no collation is specified, the default collation is used. You can run the select * from pg_collation; command to query the collation from the pg_collation system catalog.

  • SERVER server_name

    Specifies the server name of the foreign table.

  • 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 are also verified by 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 file_fdw are as follows:
      • filename

        File to be read. This parameter is required and must be an absolute path.

      • format

        File format of the remote server, which is the same as the FORMAT option in the COPY statement. The value can be text, csv, binary, or fixed.

      • header

        Specifies whether a specified file has a header, which is the same as the HEADER option of the COPY statement.

        • delimiter

          File delimiter, which is the same as the DELIMITER option of the COPY statement.

        • quote

          Quote character of a file, which is the same as the QUOTE option of the COPY statement.

        • escape

          Escape character of a file, which is the same as the ESCAPE option of the COPY statement.

        • null

          Null string of a file, which is the same as the NULL option of the COPY statement.

        • encoding

          Encoding of a file, which is the same as the ENCODING option of the COPY statement.

        • force_not_null

          This is a Boolean option. If it is true, the value of the declared column cannot match a null string (file-level null option). This option is the same as the FORCE_NOT_NULL option of the COPY statement.

      For details about how to use file_fdw, see section "Foreign Data Wrapper > file_fdw" in Feature Guide.

  • NOT NULL

    Forbids NULL values in columns.

  • NULL

    Allows NULL values in columns.

  • DEFAULT default_expr

    Assigns a default data value to a column. The value can be any variable-free expressions. (Subqueries and cross-references to other columns in the current table are not allowed.) The data type of the default expression must match the data type of the column.

    The default expression will be used in any insert operation that does not specify a value for the column. If there is no default value for a column, then the default value is NULL.

Examples

-- Create a server.
gaussdb=# CREATE SERVER my_server FOREIGN DATA WRAPPER log_fdw;

-- Create a foreign table.
gaussdb=# CREATE FOREIGN TABLE foreign_tbl (col1 text) SERVER my_server OPTIONS (logtype 'gs_log');

-- Delete the foreign table.
gaussdb=# DROP FOREIGN TABLE foreign_tbl;

-- Delete the server.
gaussdb=# DROP SERVER my_server;