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 section "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, the 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.

• To prevent arbitrary read of files on the server, system administrators and O&M administrators in O&M mode are controlled by the enable_copy_server_files and safe_data_path parameters.
• If the enable_copy_server_files parameter is disabled, only the initial user is allowed to create foreign tables of the file_fdw type. If this parameter is enabled, system administrators and O&M administrators in O&M mode can create foreign tables of the file_fdw type. This prevents unauthorized users from viewing or modifying sensitive files.
• If enable_copy_server_files is enabled, administrators can use the GUC parameter safe_data_path to set the path for users to import and export. The files must be stored in a subpath of the set path. If this GUC parameter is not set (by default), the path used by users is not blocked.

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 the value 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 (that is, file-level NULL option). 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.