CREATE FOREIGN TABLE

Function

CREATE FOREIGN TABLE 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.

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' [, ... ] ) ]

The column_constraint can be:
[ 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 identifier naming convention.
column_name

Specifies the name of a column in the foreign table.

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

Specifies the data type of the column.
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 mandatory 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 field cannot be an empty string. This option is the same as the FORCE_NOT_NULL option of the COPY statement.