Help Center/ GaussDB/ Feature Guide(Distributed_V2.0-8.x)/ Foreign Data Wrapper/ file_fdw
Updated on 2025-08-22 GMT+08:00
View PDF

file_fdw

The file_fdw module provides the foreign data wrapper file_fdw, which can be used to access data files in the file system of a server. The data file must be readable by COPY FROM. For details, see "SQL Reference > SQL Syntax > COPY" in Developer Guide. file_fdw is only used to access readable data files, but cannot write data to the data files.

By default, file_fdw is compiled in GaussDB. During database initialization, the plug-in is created in the pg_catalog schema.

The server and foreign table corresponding to file_fdw can be created only by the initial user of the database, the system administrator, or the O&M administrator when the O&M mode is enabled.

When you create a foreign table using file_fdw, you can add the following options:

  • 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 of the COPY statement. The value can be text, csv, or binary.

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

  • file_fdw does not support the OIDS and FORCE_QUOTE options of the COPY statement.
  • These options can only be declared for a foreign table or the columns of the foreign table, not for file_fdw itself, nor for the server or user mapping that uses file_fdw.
  • To modify table-level options, you must obtain the system administrator permissions. For security reasons, only the system administrator can determine the files to be read.
  • For a foreign table that uses file_fdw, running EXPLAIN displays the name and size (in bytes) of the file to be read. If the keyword COSTS OFF is specified, the file size is not displayed.

Using file_fdw

  • To create a server object, run CREATE SERVER.
  • To create a user mapping, run CREATE USER MAPPING.
  • To create a foreign table, run CREATE FOREIGN TABLE.
    • The structure of the foreign table must be consistent with the data in the specified file.
    • When a foreign table is queried, no write operation is allowed.
  • To drop a foreign table, run DROP FOREIGN TABLE.
  • To drop a user mapping, run DROP USER MAPPING.
  • To drop a server object, run DROP SERVER.

Precautions

  • To use file_fdw, you need to specify the file to be read. Prepare the file and grant the read permission on the file for the database to access the file.
  • DROP EXTENSION cannot be used for file_fdw.
Parent topic: Foreign Data Wrapper