Wiki / Foreign Data Wrappers / Server files

file_fdw

Server files

Reads server-side files or program output through the foreign-data wrapper interface.

Reading PostgreSQL 18.6.

Description

Reads server-side files or program output through the foreign-data wrapper interface.

Interface family: Foreign-data wrapper
Handler or routine: file_fdw_handler
Recorded callbacks: 10

Interface and capability boundaries

The matrix records callbacks actually registered in this source build. Registration identifies an implemented interface hook; options, query shape, privileges and provider rules determine whether an operation is allowed.

The complete same-version manual below retains configuration, constraints and examples. No runtime capability test is claimed.

File access boundary

The file_fdw module provides the foreign-data wrapper file_fdw , which can be used to access data files in the server's file system, or to execute programs on the server and read their output. The data file or program output must be in a format that can be read by COPY FROM ; see COPY for details. Access to data files is currently read-only.

Registered implementation in core source

{
	FdwRoutine *fdwroutine = makeNode(FdwRoutine);

	fdwroutine->GetForeignRelSize = fileGetForeignRelSize;
	fdwroutine->GetForeignPaths = fileGetForeignPaths;
	fdwroutine->GetForeignPlan = fileGetForeignPlan;
	fdwroutine->ExplainForeignScan = fileExplainForeignScan;
	fdwroutine->BeginForeignScan = fileBeginForeignScan;
	fdwroutine->IterateForeignScan = fileIterateForeignScan;
	fdwroutine->ReScanForeignScan = fileReScanForeignScan;
	fdwroutine->EndForeignScan = fileEndForeignScan;
	fdwroutine->AnalyzeForeignTable = fileAnalyzeForeignTable;
	fdwroutine->IsForeignScanParallelSafe = fileIsForeignScanParallelSafe;

	PG_RETURN_POINTER(fdwroutine);
}

Registered interface handlers

Interface operation	Source observation	Callback	Implementation
Scan rows	Handler registered; conditions apply	IterateForeignScan	fileIterateForeignScan
INSERT	No handler registered in this build	ExecForeignInsert	Not registered
UPDATE	No handler registered in this build	ExecForeignUpdate	Not registered
DELETE	No handler registered in this build	ExecForeignDelete	Not registered
TRUNCATE	No handler registered in this build	ExecForeignTruncate	Not registered
Join path pushdown	No handler registered in this build	GetForeignJoinPaths	Not registered
Upper paths, including aggregation	No handler registered in this build	GetForeignUpperPaths	Not registered
Batch insert	No handler registered in this build	ExecForeignBatchInsert	Not registered
Asynchronous append execution	No handler registered in this build	ForeignAsyncRequest	Not registered
Import schema	No handler registered in this build	ImportForeignSchema	Not registered

Documented options

Option	Same-version definition
filename	Specifies the file to be read. Relative paths are relative to the data directory. Either filename or program must be specified, but not both.
program	Specifies the command to be executed. The standard output of this command will be read as though COPY FROM PROGRAM were used. Either program or filename must be specified, but not both.
format	Specifies the data format, the same as COPY 's FORMAT option.
header	Specifies whether the data has a header line, the same as COPY 's HEADER option.
delimiter	Specifies the data delimiter character, the same as COPY 's DELIMITER option.
quote	Specifies the data quote character, the same as COPY 's QUOTE option.
escape	Specifies the data escape character, the same as COPY 's ESCAPE option.
null	Specifies the data null string, the same as COPY 's NULL option.
default	Specifies the string that represents a default value, the same as COPY 's DEFAULT option.
encoding	Specifies the data encoding, the same as COPY 's ENCODING option.
on_error	Specifies how to behave when encountering an error converting a column's input value into its data type, the same as COPY 's ON_ERROR option.
reject_limit	Specifies the maximum number of errors tolerated while converting a column's input value to its data type, the same as COPY 's REJECT_LIMIT option.
log_verbosity	Specifies the amount of messages emitted by file_fdw , the same as COPY 's LOG_VERBOSITY option.
force_not_null	This is a Boolean option. If true, it specifies that values of the column should not be matched against the null string (that is, the table-level null option). This has the same effect as listing the column in COPY 's FORCE_NOT_NULL option.
force_null	This is a Boolean option. If true, it specifies that values of the column which match the null string are returned as NULL even if the value is quoted. Without this option, only unquoted values matching the null string are returned as NULL . This has the same effect as listing the column in COPY 's FORCE_NULL option.

Manual definition

F.15. file_fdw — access data files in the server's file system

The file_fdw module provides the foreign-data wrapper file_fdw, which can be used to access data files in the server's file system, or to execute programs on the server and read their output. The data file or program output must be in a format that can be read by COPY FROM; see COPY for details. Access to data files is currently read-only.

A foreign table created using this wrapper can have the following options:

filename: Specifies the file to be read. Relative paths are relative to the data directory. Either filename or program must be specified, but not both.
program: Specifies the command to be executed. The standard output of this command will be read as though COPY FROM PROGRAM were used. Either program or filename must be specified, but not both.
format: Specifies the data format, the same as COPY's FORMAT option.
header: Specifies whether the data has a header line, the same as COPY's HEADER option.
delimiter: Specifies the data delimiter character, the same as COPY's DELIMITER option.
quote: Specifies the data quote character, the same as COPY's QUOTE option.
escape: Specifies the data escape character, the same as COPY's ESCAPE option.
null: Specifies the data null string, the same as COPY's NULL option.
default: Specifies the string that represents a default value, the same as COPY's DEFAULT option.
encoding: Specifies the data encoding, the same as COPY's ENCODING option.
on_error: Specifies how to behave when encountering an error converting a column's input value into its data type, the same as COPY's ON_ERROR option.
reject_limit: Specifies the maximum number of errors tolerated while converting a column's input value to its data type, the same as COPY's REJECT_LIMIT option.
log_verbosity: Specifies the amount of messages emitted by file_fdw, the same as COPY's LOG_VERBOSITY option.

Note that while COPY allows options such as HEADER to be specified without a corresponding value, the foreign table option syntax requires a value to be present in all cases. To activate COPY options typically written without a value, you can pass the value TRUE, since all such options are Booleans.

A column of a foreign table created using this wrapper can have the following options:

force_not_null: This is a Boolean option. If true, it specifies that values of the column should not be matched against the null string (that is, the table-level null option). This has the same effect as listing the column in COPY's FORCE_NOT_NULL option.
force_null: This is a Boolean option. If true, it specifies that values of the column which match the null string are returned as NULL even if the value is quoted. Without this option, only unquoted values matching the null string are returned as NULL. This has the same effect as listing the column in COPY's FORCE_NULL option.

COPY's FORCE_QUOTE option is currently not supported by file_fdw.

These options can only be specified for a foreign table or its columns, not in the options of the file_fdw foreign-data wrapper, nor in the options of a server or user mapping using the wrapper.

Changing table-level options requires being a superuser or having the privileges of the role pg_read_server_files (to use a filename) or the role pg_execute_server_program (to use a program), for security reasons: only certain users should be able to control which file is read or which program is run. In principle regular users could be allowed to change the other options, but that's not supported at present.

When specifying the program option, keep in mind that the option string is executed by the shell. If you need to pass any arguments to the command that come from an untrusted source, you must be careful to strip or escape any characters that might have special meaning to the shell. For security reasons, it is best to use a fixed command string, or at least avoid passing any user input in it.

For a foreign table using file_fdw, EXPLAIN shows the name of the file to be read or program to be run. For a file, unless COSTS OFF is specified, the file size (in bytes) is shown as well.

Example F.1. Create a Foreign Table for PostgreSQL CSV Logs

One of the obvious uses for file_fdw is to make the PostgreSQL activity log available as a table for querying. To do this, first you must be logging to a CSV file, which here we will call pglog.csv. First, install file_fdw as an extension:

CREATE EXTENSION file_fdw;

Then create a foreign server:

CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

Now you are ready to create the foreign data table. Using the CREATE FOREIGN TABLE command, you will need to define the columns for the table, the CSV file name, and its format:

CREATE FOREIGN TABLE pglog (
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text,
  backend_type text,
  leader_pid integer,
  query_id bigint
) SERVER pglog
OPTIONS ( filename 'log/pglog.csv', format 'csv' );

That's it — now you can query your log directly. In production, of course, you would need to define some way to deal with log rotation.

Example F.2. Create a Foreign Table with an Option on a Column

To set the force_null option for a column, use the OPTIONS keyword.

CREATE FOREIGN TABLE films (
 code char(5) NOT NULL,
 title text NOT NULL,
 rating text OPTIONS (force_null 'true')
) SERVER film_server
OPTIONS ( filename 'films/db.csv', format 'csv' );

Related entries

Documentation and source

Source build

Version: 18.6
Build: PostgreSQL 18.6 source archive
Source fingerprint: 555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f

Compare versions

PostgreSQL 18 → 19: changed.

--- PostgreSQL 18
+++ PostgreSQL 19
@@ -25,7 +25,7 @@
       "name": "format"
     },
     {
-      "definition": "Specifies whether the data has a header line, the same as COPY 's HEADER option.",
+      "definition": "Specifies whether to skip a header line, or how many header lines to skip, the same as COPY 's HEADER option.",
       "name": "header"
     },
     {

Compares recorded interfaces and attributes. Source fingerprints and build metadata are excluded; an absent sample is not proof of the introduction or removal release.

Export JSON · Back to Foreign Data Wrappers · Recorded in PostgreSQL 10 through 20; the first sample is not necessarily its introduction.