{"Entry":{"collection":"fdw","key":"file_fdw","name":"file_fdw","aliases":[],"metadata":{"aliases":[],"category":"Server files","content_hash":"c584bcf961f646facb34f819b6db7b99a0e4024434cff56684c2c803ac8bb38f","imported_at":"2026-09-30T00:40:37.170312+08:00","name":"file_fdw","name_zh":"Server files","slug":"file_fdw","summary":"Reads server-side files or program output through the foreign-data wrapper interface."}},"Definition":{"Collection":"fdw","Key":"file_fdw","SourceDatabase":"center","Version":"18","SourceTable":"foreign_data_wrapper","SourceKey":"file_fdw","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","Facts":{"callbacks":{"AnalyzeForeignTable":"fileAnalyzeForeignTable","BeginForeignScan":"fileBeginForeignScan","EndForeignScan":"fileEndForeignScan","ExplainForeignScan":"fileExplainForeignScan","GetForeignPaths":"fileGetForeignPaths","GetForeignPlan":"fileGetForeignPlan","GetForeignRelSize":"fileGetForeignRelSize","IsForeignScanParallelSafe":"fileIsForeignScanParallelSafe","IterateForeignScan":"fileIterateForeignScan","ReScanForeignScan":"fileReScanForeignScan"},"comparison_data":{"callbacks":{"AnalyzeForeignTable":"fileAnalyzeForeignTable","BeginForeignScan":"fileBeginForeignScan","EndForeignScan":"fileEndForeignScan","ExplainForeignScan":"fileExplainForeignScan","GetForeignPaths":"fileGetForeignPaths","GetForeignPlan":"fileGetForeignPlan","GetForeignRelSize":"fileGetForeignRelSize","IsForeignScanParallelSafe":"fileIsForeignScanParallelSafe","IterateForeignScan":"fileIterateForeignScan","ReScanForeignScan":"fileReScanForeignScan"},"options":[{"definition":"Specifies the file to be read. Relative paths are relative to the data directory. Either filename or program must be specified, but not both.","name":"filename"},{"definition":"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.","name":"program"},{"definition":"Specifies the data format, the same as COPY 's FORMAT option.","name":"format"},{"definition":"Specifies whether the data has a header line, the same as COPY 's HEADER option.","name":"header"},{"definition":"Specifies the data delimiter character, the same as COPY 's DELIMITER option.","name":"delimiter"},{"definition":"Specifies the data quote character, the same as COPY 's QUOTE option.","name":"quote"},{"definition":"Specifies the data escape character, the same as COPY 's ESCAPE option.","name":"escape"},{"definition":"Specifies the data null string, the same as COPY 's NULL option.","name":"null"},{"definition":"Specifies the string that represents a default value, the same as COPY 's DEFAULT option.","name":"default"},{"definition":"Specifies the data encoding, the same as COPY 's ENCODING option.","name":"encoding"},{"definition":"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.","name":"on_error"},{"definition":"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.","name":"reject_limit"},{"definition":"Specifies the amount of messages emitted by file_fdw , the same as COPY 's LOG_VERBOSITY option.","name":"log_verbosity"},{"definition":"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.","name":"force_not_null"},{"definition":"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.","name":"force_null"}],"protocol_versions":{},"source_options":[]},"comparison_hash":"3a50d8e658a12e9ec3405a8fd6dae6ec70a2fe3837297394492fb97a95b01306","description":["Reads server-side files or program output through the foreign-data wrapper interface."],"evidence_kind":"source and documentation","facts":[{"label":"Interface family","value":"Foreign-data wrapper"},{"label":"Handler or routine","value":"file_fdw_handler"},{"label":"Recorded callbacks","value":"10"}],"manual_html":"\u003cdiv class=\"sect1\" id=\"FILE-FDW\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003eF.15. file_fdw — access data files in the server's file system \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003ccode class=\"filename\"\u003efile_fdw\u003c/code\u003e module provides the foreign-data wrapper \u003ccode class=\"function\"\u003efile_fdw\u003c/code\u003e, 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 \u003ccode class=\"command\"\u003eCOPY FROM\u003c/code\u003e; see \u003ca class=\"xref\" href=\"/docs/18/sql-copy.html\" title=\"COPY\"\u003e\u003cspan class=\"refentrytitle\"\u003eCOPY\u003c/span\u003e\u003c/a\u003e for details. Access to data files is currently read-only.\u003c/p\u003e\n\u003cp\u003eA foreign table created using this wrapper can have the following options:\u003c/p\u003e\n\u003cdiv class=\"variablelist\"\u003e\n\u003cdl class=\"variablelist\"\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003efilename\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the file to be read. Relative paths are relative to the data directory. Either \u003ccode class=\"literal\"\u003efilename\u003c/code\u003e or \u003ccode class=\"literal\"\u003eprogram\u003c/code\u003e must be specified, but not both.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eprogram\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the command to be executed. The standard output of this command will be read as though \u003ccode class=\"command\"\u003eCOPY FROM PROGRAM\u003c/code\u003e were used. Either \u003ccode class=\"literal\"\u003eprogram\u003c/code\u003e or \u003ccode class=\"literal\"\u003efilename\u003c/code\u003e must be specified, but not both.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eformat\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data format, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eFORMAT\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eheader\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies whether the data has a header line, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eHEADER\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003edelimiter\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data delimiter character, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eDELIMITER\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003equote\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data quote character, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eQUOTE\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eescape\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data escape character, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eESCAPE\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003enull\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data null string, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003edefault\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the string that represents a default value, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eencoding\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data encoding, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eENCODING\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eon_error\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies how to behave when encountering an error converting a column's input value into its data type, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eON_ERROR\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003ereject_limit\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the maximum number of errors tolerated while converting a column's input value to its data type, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eREJECT_LIMIT\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003elog_verbosity\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the amount of messages emitted by \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eLOG_VERBOSITY\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003c/dl\u003e\n\u003c/div\u003e\n\u003cp\u003eNote that while \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e allows options such as \u003ccode class=\"literal\"\u003eHEADER\u003c/code\u003e to be specified without a corresponding value, the foreign table option syntax requires a value to be present in all cases. To activate \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e options typically written without a value, you can pass the value TRUE, since all such options are Booleans.\u003c/p\u003e\n\u003cp\u003eA column of a foreign table created using this wrapper can have the following options:\u003c/p\u003e\n\u003cdiv class=\"variablelist\"\u003e\n\u003cdl class=\"variablelist\"\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eforce_not_null\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eThis 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 \u003ccode class=\"literal\"\u003enull\u003c/code\u003e option). This has the same effect as listing the column in \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eFORCE_NOT_NULL\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eforce_null\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eThis is a Boolean option. If true, it specifies that values of the column which match the null string are returned as \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e even if the value is quoted. Without this option, only unquoted values matching the null string are returned as \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e. This has the same effect as listing the column in \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eFORCE_NULL\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003c/dl\u003e\n\u003c/div\u003e\n\u003cp\u003e\u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eFORCE_QUOTE\u003c/code\u003e option is currently not supported by \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThese options can only be specified for a foreign table or its columns, not in the options of the \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e foreign-data wrapper, nor in the options of a server or user mapping using the wrapper.\u003c/p\u003e\n\u003cp\u003eChanging table-level options requires being a superuser or having the privileges of the role \u003ccode class=\"literal\"\u003epg_read_server_files\u003c/code\u003e (to use a filename) or the role \u003ccode class=\"literal\"\u003epg_execute_server_program\u003c/code\u003e (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.\u003c/p\u003e\n\u003cp\u003eWhen specifying the \u003ccode class=\"literal\"\u003eprogram\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eFor a foreign table using \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e, \u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e shows the name of the file to be read or program to be run. For a file, unless \u003ccode class=\"literal\"\u003eCOSTS OFF\u003c/code\u003e is specified, the file size (in bytes) is shown as well.\u003c/p\u003e\n\u003cdiv class=\"example\" id=\"id-1.11.7.25.14\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eExample F.1. Create a Foreign Table for PostgreSQL CSV Logs\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"example-contents\"\u003e\n\u003cp\u003eOne of the obvious uses for \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e is to make the PostgreSQL activity log available as a table for querying. To do this, first you must be \u003ca class=\"link\" href=\"/docs/18/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG\" title=\"19.8.4. Using CSV-Format Log Output\"\u003elogging to a CSV file,\u003c/a\u003e which here we will call \u003ccode class=\"literal\"\u003epglog.csv\u003c/code\u003e. First, install \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e as an extension:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE EXTENSION file_fdw;\n\u003c/pre\u003e\n\u003cp\u003eThen create a foreign server:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;\n\u003c/pre\u003e\n\u003cp\u003eNow you are ready to create the foreign data table. Using the \u003ccode class=\"command\"\u003eCREATE FOREIGN TABLE\u003c/code\u003e command, you will need to define the columns for the table, the CSV file name, and its format:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE FOREIGN TABLE pglog (\n  log_time timestamp(3) with time zone,\n  user_name text,\n  database_name text,\n  process_id integer,\n  connection_from text,\n  session_id text,\n  session_line_num bigint,\n  command_tag text,\n  session_start_time timestamp with time zone,\n  virtual_transaction_id text,\n  transaction_id bigint,\n  error_severity text,\n  sql_state_code text,\n  message text,\n  detail text,\n  hint text,\n  internal_query text,\n  internal_query_pos integer,\n  context text,\n  query text,\n  query_pos integer,\n  location text,\n  application_name text,\n  backend_type text,\n  leader_pid integer,\n  query_id bigint\n) SERVER pglog\nOPTIONS ( filename 'log/pglog.csv', format 'csv' );\n\u003c/pre\u003e\n\u003cp\u003eThat'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.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"example-break\"\u003e\n\u003cdiv class=\"example\" id=\"id-1.11.7.25.15\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eExample F.2. Create a Foreign Table with an Option on a Column\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"example-contents\"\u003e\n\u003cp\u003eTo set the \u003ccode class=\"literal\"\u003eforce_null\u003c/code\u003e option for a column, use the \u003ccode class=\"literal\"\u003eOPTIONS\u003c/code\u003e keyword.\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE FOREIGN TABLE films (\n code char(5) NOT NULL,\n title text NOT NULL,\n rating text OPTIONS (force_null 'true')\n) SERVER film_server\nOPTIONS ( filename 'films/db.csv', format 'csv' );\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"example-break\"\u003e\n\u003c/div\u003e","manual_path":"/docs/18/file-fdw.html","related":[{"label":"Foreign-data wrapper catalog","url":"/wiki/catalog/pg_foreign_data_wrapper/?v=18"},{"label":"Existing extension catalogue identity","url":"/e/file_fdw/"},{"label":"Foreign Scan plan node","url":"/wiki/plan/foreign-scan/?v=18"}],"release":{"channel":"stable","label":"18.6","major":"18","ref":"PostgreSQL 18.6 source archive","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_snapshot_utc":"","source_url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},"runtime_verified":false,"sections":[{"paragraphs":["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."],"title":"Interface and capability boundaries"},{"paragraphs":["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."],"title":"File access boundary"},{"code":"{\n\tFdwRoutine *fdwroutine = makeNode(FdwRoutine);\n\n\tfdwroutine-\u003eGetForeignRelSize = fileGetForeignRelSize;\n\tfdwroutine-\u003eGetForeignPaths = fileGetForeignPaths;\n\tfdwroutine-\u003eGetForeignPlan = fileGetForeignPlan;\n\tfdwroutine-\u003eExplainForeignScan = fileExplainForeignScan;\n\tfdwroutine-\u003eBeginForeignScan = fileBeginForeignScan;\n\tfdwroutine-\u003eIterateForeignScan = fileIterateForeignScan;\n\tfdwroutine-\u003eReScanForeignScan = fileReScanForeignScan;\n\tfdwroutine-\u003eEndForeignScan = fileEndForeignScan;\n\tfdwroutine-\u003eAnalyzeForeignTable = fileAnalyzeForeignTable;\n\tfdwroutine-\u003eIsForeignScanParallelSafe = fileIsForeignScanParallelSafe;\n\n\tPG_RETURN_POINTER(fdwroutine);\n}","title":"Registered implementation in core source"}],"sources":[{"label":"PostgreSQL 18 English manual","path":"file-fdw.html","sha256":"5da35cee02e73ec6656a2020d8351046cad9283a137b45064bee58f1ff7eb787","url":"/docs/18/file-fdw.html"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"contrib/file_fdw/file_fdw.c:183","line":183,"path":"contrib/file_fdw/file_fdw.c","sha256":"d79ec21c08be6a806b0710aab09698678ddb24103d3470702e66ee1523432125","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"}],"tables":[{"columns":[{"key":"feature","label":"Interface operation"},{"key":"state","label":"Source observation"},{"key":"callback","label":"Callback"},{"key":"implementation","label":"Implementation"}],"key":"callbacks","rows":[{"callback":"IterateForeignScan","feature":"Scan rows","implementation":"fileIterateForeignScan","state":"Handler registered; conditions apply"},{"callback":"ExecForeignInsert","feature":"INSERT","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ExecForeignUpdate","feature":"UPDATE","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ExecForeignDelete","feature":"DELETE","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ExecForeignTruncate","feature":"TRUNCATE","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"GetForeignJoinPaths","feature":"Join path pushdown","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"GetForeignUpperPaths","feature":"Upper paths, including aggregation","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ExecForeignBatchInsert","feature":"Batch insert","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ForeignAsyncRequest","feature":"Asynchronous append execution","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ImportForeignSchema","feature":"Import schema","implementation":"Not registered","state":"No handler registered in this build"}],"title":"Registered interface handlers"},{"columns":[{"key":"name","label":"Option"},{"key":"definition","label":"Same-version definition"}],"key":"options","rows":[{"definition":"Specifies the file to be read. Relative paths are relative to the data directory. Either filename or program must be specified, but not both.","name":"filename"},{"definition":"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.","name":"program"},{"definition":"Specifies the data format, the same as COPY 's FORMAT option.","name":"format"},{"definition":"Specifies whether the data has a header line, the same as COPY 's HEADER option.","name":"header"},{"definition":"Specifies the data delimiter character, the same as COPY 's DELIMITER option.","name":"delimiter"},{"definition":"Specifies the data quote character, the same as COPY 's QUOTE option.","name":"quote"},{"definition":"Specifies the data escape character, the same as COPY 's ESCAPE option.","name":"escape"},{"definition":"Specifies the data null string, the same as COPY 's NULL option.","name":"null"},{"definition":"Specifies the string that represents a default value, the same as COPY 's DEFAULT option.","name":"default"},{"definition":"Specifies the data encoding, the same as COPY 's ENCODING option.","name":"encoding"},{"definition":"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.","name":"on_error"},{"definition":"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.","name":"reject_limit"},{"definition":"Specifies the amount of messages emitted by file_fdw , the same as COPY 's LOG_VERBOSITY option.","name":"log_verbosity"},{"definition":"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.","name":"force_not_null"},{"definition":"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.","name":"force_null"}],"title":"Documented options"}]},"ManualEvidence":{"manual_path":"/docs/18/file-fdw.html","release":{"channel":"stable","label":"18.6","major":"18","ref":"PostgreSQL 18.6 source archive","revision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","source_snapshot_utc":"","source_url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"},"sources":[{"label":"PostgreSQL 18 English manual","path":"file-fdw.html","sha256":"5da35cee02e73ec6656a2020d8351046cad9283a137b45064bee58f1ff7eb787","url":"/docs/18/file-fdw.html"},{"archive_sha256":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","label":"contrib/file_fdw/file_fdw.c:183","line":183,"path":"contrib/file_fdw/file_fdw.c","sha256":"d79ec21c08be6a806b0710aab09698678ddb24103d3470702e66ee1523432125","url":"https://ftp.postgresql.org/pub/source/v18.6/postgresql-18.6.tar.bz2"}]},"MeasuredEvidence":{"runtime_verified":false}},"Text":{"Collection":"fdw","Key":"file_fdw","SourceDatabase":"center","Version":"18","Locale":"en","Title":"file_fdw","Summary":"Reads server-side files or program output through the foreign-data wrapper interface.","BodyHTML":"\u003cdiv id=\"FILE-FDW\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2\u003eF.15. file_fdw — access data files in the server\u0026#39;s file system \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003ccode\u003efile_fdw\u003c/code\u003e module provides the foreign-data wrapper \u003ccode\u003efile_fdw\u003c/code\u003e, which can be used to access data files in the server\u0026#39;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 \u003ccode\u003eCOPY FROM\u003c/code\u003e; see \u003ca href=\"/docs/18/sql-copy.html\" title=\"COPY\" rel=\"nofollow\"\u003e\u003cspan\u003eCOPY\u003c/span\u003e\u003c/a\u003e for details. Access to data files is currently read-only.\u003c/p\u003e\n\u003cp\u003eA foreign table created using this wrapper can have the following options:\u003c/p\u003e\n\u003cdiv\u003e\n\u003cdl\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003efilename\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the file to be read. Relative paths are relative to the data directory. Either \u003ccode\u003efilename\u003c/code\u003e or \u003ccode\u003eprogram\u003c/code\u003e must be specified, but not both.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eprogram\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the command to be executed. The standard output of this command will be read as though \u003ccode\u003eCOPY FROM PROGRAM\u003c/code\u003e were used. Either \u003ccode\u003eprogram\u003c/code\u003e or \u003ccode\u003efilename\u003c/code\u003e must be specified, but not both.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eformat\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data format, the same as \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eFORMAT\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eheader\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies whether the data has a header line, the same as \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eHEADER\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003edelimiter\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data delimiter character, the same as \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eDELIMITER\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003equote\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data quote character, the same as \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eQUOTE\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eescape\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data escape character, the same as \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eESCAPE\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003enull\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data null string, the same as \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eNULL\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003edefault\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the string that represents a default value, the same as \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eDEFAULT\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eencoding\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data encoding, the same as \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eENCODING\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eon_error\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies how to behave when encountering an error converting a column\u0026#39;s input value into its data type, the same as \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eON_ERROR\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003ereject_limit\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the maximum number of errors tolerated while converting a column\u0026#39;s input value to its data type, the same as \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eREJECT_LIMIT\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003elog_verbosity\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the amount of messages emitted by \u003ccode\u003efile_fdw\u003c/code\u003e, the same as \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eLOG_VERBOSITY\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003c/dl\u003e\n\u003c/div\u003e\n\u003cp\u003eNote that while \u003ccode\u003eCOPY\u003c/code\u003e allows options such as \u003ccode\u003eHEADER\u003c/code\u003e to be specified without a corresponding value, the foreign table option syntax requires a value to be present in all cases. To activate \u003ccode\u003eCOPY\u003c/code\u003e options typically written without a value, you can pass the value TRUE, since all such options are Booleans.\u003c/p\u003e\n\u003cp\u003eA column of a foreign table created using this wrapper can have the following options:\u003c/p\u003e\n\u003cdiv\u003e\n\u003cdl\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eforce_not_null\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eThis 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 \u003ccode\u003enull\u003c/code\u003e option). This has the same effect as listing the column in \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eFORCE_NOT_NULL\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eforce_null\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eThis is a Boolean option. If true, it specifies that values of the column which match the null string are returned as \u003ccode\u003eNULL\u003c/code\u003e even if the value is quoted. Without this option, only unquoted values matching the null string are returned as \u003ccode\u003eNULL\u003c/code\u003e. This has the same effect as listing the column in \u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eFORCE_NULL\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003c/dl\u003e\n\u003c/div\u003e\n\u003cp\u003e\u003ccode\u003eCOPY\u003c/code\u003e\u0026#39;s \u003ccode\u003eFORCE_QUOTE\u003c/code\u003e option is currently not supported by \u003ccode\u003efile_fdw\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThese options can only be specified for a foreign table or its columns, not in the options of the \u003ccode\u003efile_fdw\u003c/code\u003e foreign-data wrapper, nor in the options of a server or user mapping using the wrapper.\u003c/p\u003e\n\u003cp\u003eChanging table-level options requires being a superuser or having the privileges of the role \u003ccode\u003epg_read_server_files\u003c/code\u003e (to use a filename) or the role \u003ccode\u003epg_execute_server_program\u003c/code\u003e (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\u0026#39;s not supported at present.\u003c/p\u003e\n\u003cp\u003eWhen specifying the \u003ccode\u003eprogram\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eFor a foreign table using \u003ccode\u003efile_fdw\u003c/code\u003e, \u003ccode\u003eEXPLAIN\u003c/code\u003e shows the name of the file to be read or program to be run. For a file, unless \u003ccode\u003eCOSTS OFF\u003c/code\u003e is specified, the file size (in bytes) is shown as well.\u003c/p\u003e\n\u003cdiv id=\"id-1.11.7.25.14\"\u003e\n\u003cp\u003e\u003cstrong\u003eExample F.1. Create a Foreign Table for PostgreSQL CSV Logs\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv\u003e\n\u003cp\u003eOne of the obvious uses for \u003ccode\u003efile_fdw\u003c/code\u003e is to make the PostgreSQL activity log available as a table for querying. To do this, first you must be \u003ca href=\"/docs/18/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG\" rel=\"nofollow\"\u003elogging to a CSV file,\u003c/a\u003e which here we will call \u003ccode\u003epglog.csv\u003c/code\u003e. First, install \u003ccode\u003efile_fdw\u003c/code\u003e as an extension:\u003c/p\u003e\n\u003cpre\u003eCREATE EXTENSION file_fdw;\n\u003c/pre\u003e\n\u003cp\u003eThen create a foreign server:\u003c/p\u003e\n\u003cpre\u003eCREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;\n\u003c/pre\u003e\n\u003cp\u003eNow you are ready to create the foreign data table. Using the \u003ccode\u003eCREATE FOREIGN TABLE\u003c/code\u003e command, you will need to define the columns for the table, the CSV file name, and its format:\u003c/p\u003e\n\u003cpre\u003eCREATE FOREIGN TABLE pglog (\n  log_time timestamp(3) with time zone,\n  user_name text,\n  database_name text,\n  process_id integer,\n  connection_from text,\n  session_id text,\n  session_line_num bigint,\n  command_tag text,\n  session_start_time timestamp with time zone,\n  virtual_transaction_id text,\n  transaction_id bigint,\n  error_severity text,\n  sql_state_code text,\n  message text,\n  detail text,\n  hint text,\n  internal_query text,\n  internal_query_pos integer,\n  context text,\n  query text,\n  query_pos integer,\n  location text,\n  application_name text,\n  backend_type text,\n  leader_pid integer,\n  query_id bigint\n) SERVER pglog\nOPTIONS ( filename \u0026#39;log/pglog.csv\u0026#39;, format \u0026#39;csv\u0026#39; );\n\u003c/pre\u003e\n\u003cp\u003eThat\u0026#39;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.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr\u003e\n\u003cdiv id=\"id-1.11.7.25.15\"\u003e\n\u003cp\u003e\u003cstrong\u003eExample F.2. Create a Foreign Table with an Option on a Column\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv\u003e\n\u003cp\u003eTo set the \u003ccode\u003eforce_null\u003c/code\u003e option for a column, use the \u003ccode\u003eOPTIONS\u003c/code\u003e keyword.\u003c/p\u003e\n\u003cpre\u003eCREATE FOREIGN TABLE films (\n code char(5) NOT NULL,\n title text NOT NULL,\n rating text OPTIONS (force_null \u0026#39;true\u0026#39;)\n) SERVER film_server\nOPTIONS ( filename \u0026#39;films/db.csv\u0026#39;, format \u0026#39;csv\u0026#39; );\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr\u003e\n\u003c/div\u003e","SourceRevision":"555610c24d53e4316da5b7d3fc25c279d96856d5e0e23ee308c328c5fa881d9f","ContentHash":"89275d1960845d8abf81e25ea510237a64022e52e9f2adc3ce0c0025aeade3ca","Payload":{"description":["Reads server-side files or program output through the foreign-data wrapper interface."],"manual_html":"\u003cdiv class=\"sect1\" id=\"FILE-FDW\"\u003e\n\u003cdiv class=\"titlepage\"\u003e\n\u003cdiv\u003e\n\u003cdiv\u003e\n\u003ch2 class=\"title\"\u003eF.15. file_fdw — access data files in the server's file system \u003c/h2\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003c/div\u003e\n\u003cp\u003eThe \u003ccode class=\"filename\"\u003efile_fdw\u003c/code\u003e module provides the foreign-data wrapper \u003ccode class=\"function\"\u003efile_fdw\u003c/code\u003e, 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 \u003ccode class=\"command\"\u003eCOPY FROM\u003c/code\u003e; see \u003ca class=\"xref\" href=\"/docs/18/sql-copy.html\" title=\"COPY\"\u003e\u003cspan class=\"refentrytitle\"\u003eCOPY\u003c/span\u003e\u003c/a\u003e for details. Access to data files is currently read-only.\u003c/p\u003e\n\u003cp\u003eA foreign table created using this wrapper can have the following options:\u003c/p\u003e\n\u003cdiv class=\"variablelist\"\u003e\n\u003cdl class=\"variablelist\"\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003efilename\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the file to be read. Relative paths are relative to the data directory. Either \u003ccode class=\"literal\"\u003efilename\u003c/code\u003e or \u003ccode class=\"literal\"\u003eprogram\u003c/code\u003e must be specified, but not both.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eprogram\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the command to be executed. The standard output of this command will be read as though \u003ccode class=\"command\"\u003eCOPY FROM PROGRAM\u003c/code\u003e were used. Either \u003ccode class=\"literal\"\u003eprogram\u003c/code\u003e or \u003ccode class=\"literal\"\u003efilename\u003c/code\u003e must be specified, but not both.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eformat\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data format, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eFORMAT\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eheader\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies whether the data has a header line, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eHEADER\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003edelimiter\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data delimiter character, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eDELIMITER\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003equote\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data quote character, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eQUOTE\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eescape\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data escape character, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eESCAPE\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003enull\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data null string, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003edefault\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the string that represents a default value, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eDEFAULT\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eencoding\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the data encoding, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eENCODING\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eon_error\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies how to behave when encountering an error converting a column's input value into its data type, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eON_ERROR\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003ereject_limit\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the maximum number of errors tolerated while converting a column's input value to its data type, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eREJECT_LIMIT\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003elog_verbosity\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eSpecifies the amount of messages emitted by \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e, the same as \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eLOG_VERBOSITY\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003c/dl\u003e\n\u003c/div\u003e\n\u003cp\u003eNote that while \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e allows options such as \u003ccode class=\"literal\"\u003eHEADER\u003c/code\u003e to be specified without a corresponding value, the foreign table option syntax requires a value to be present in all cases. To activate \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e options typically written without a value, you can pass the value TRUE, since all such options are Booleans.\u003c/p\u003e\n\u003cp\u003eA column of a foreign table created using this wrapper can have the following options:\u003c/p\u003e\n\u003cdiv class=\"variablelist\"\u003e\n\u003cdl class=\"variablelist\"\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eforce_not_null\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eThis 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 \u003ccode class=\"literal\"\u003enull\u003c/code\u003e option). This has the same effect as listing the column in \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eFORCE_NOT_NULL\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eforce_null\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\n\u003cdd\u003e\n\u003cp\u003eThis is a Boolean option. If true, it specifies that values of the column which match the null string are returned as \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e even if the value is quoted. Without this option, only unquoted values matching the null string are returned as \u003ccode class=\"literal\"\u003eNULL\u003c/code\u003e. This has the same effect as listing the column in \u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eFORCE_NULL\u003c/code\u003e option.\u003c/p\u003e\n\u003c/dd\u003e\n\u003c/dl\u003e\n\u003c/div\u003e\n\u003cp\u003e\u003ccode class=\"command\"\u003eCOPY\u003c/code\u003e's \u003ccode class=\"literal\"\u003eFORCE_QUOTE\u003c/code\u003e option is currently not supported by \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e.\u003c/p\u003e\n\u003cp\u003eThese options can only be specified for a foreign table or its columns, not in the options of the \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e foreign-data wrapper, nor in the options of a server or user mapping using the wrapper.\u003c/p\u003e\n\u003cp\u003eChanging table-level options requires being a superuser or having the privileges of the role \u003ccode class=\"literal\"\u003epg_read_server_files\u003c/code\u003e (to use a filename) or the role \u003ccode class=\"literal\"\u003epg_execute_server_program\u003c/code\u003e (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.\u003c/p\u003e\n\u003cp\u003eWhen specifying the \u003ccode class=\"literal\"\u003eprogram\u003c/code\u003e 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.\u003c/p\u003e\n\u003cp\u003eFor a foreign table using \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e, \u003ccode class=\"command\"\u003eEXPLAIN\u003c/code\u003e shows the name of the file to be read or program to be run. For a file, unless \u003ccode class=\"literal\"\u003eCOSTS OFF\u003c/code\u003e is specified, the file size (in bytes) is shown as well.\u003c/p\u003e\n\u003cdiv class=\"example\" id=\"id-1.11.7.25.14\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eExample F.1. Create a Foreign Table for PostgreSQL CSV Logs\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"example-contents\"\u003e\n\u003cp\u003eOne of the obvious uses for \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e is to make the PostgreSQL activity log available as a table for querying. To do this, first you must be \u003ca class=\"link\" href=\"/docs/18/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG\" title=\"19.8.4. Using CSV-Format Log Output\"\u003elogging to a CSV file,\u003c/a\u003e which here we will call \u003ccode class=\"literal\"\u003epglog.csv\u003c/code\u003e. First, install \u003ccode class=\"literal\"\u003efile_fdw\u003c/code\u003e as an extension:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE EXTENSION file_fdw;\n\u003c/pre\u003e\n\u003cp\u003eThen create a foreign server:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;\n\u003c/pre\u003e\n\u003cp\u003eNow you are ready to create the foreign data table. Using the \u003ccode class=\"command\"\u003eCREATE FOREIGN TABLE\u003c/code\u003e command, you will need to define the columns for the table, the CSV file name, and its format:\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE FOREIGN TABLE pglog (\n  log_time timestamp(3) with time zone,\n  user_name text,\n  database_name text,\n  process_id integer,\n  connection_from text,\n  session_id text,\n  session_line_num bigint,\n  command_tag text,\n  session_start_time timestamp with time zone,\n  virtual_transaction_id text,\n  transaction_id bigint,\n  error_severity text,\n  sql_state_code text,\n  message text,\n  detail text,\n  hint text,\n  internal_query text,\n  internal_query_pos integer,\n  context text,\n  query text,\n  query_pos integer,\n  location text,\n  application_name text,\n  backend_type text,\n  leader_pid integer,\n  query_id bigint\n) SERVER pglog\nOPTIONS ( filename 'log/pglog.csv', format 'csv' );\n\u003c/pre\u003e\n\u003cp\u003eThat'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.\u003c/p\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"example-break\"\u003e\n\u003cdiv class=\"example\" id=\"id-1.11.7.25.15\"\u003e\n\u003cp class=\"title\"\u003e\u003cstrong\u003eExample F.2. Create a Foreign Table with an Option on a Column\u003c/strong\u003e\u003c/p\u003e\n\u003cdiv class=\"example-contents\"\u003e\n\u003cp\u003eTo set the \u003ccode class=\"literal\"\u003eforce_null\u003c/code\u003e option for a column, use the \u003ccode class=\"literal\"\u003eOPTIONS\u003c/code\u003e keyword.\u003c/p\u003e\n\u003cpre class=\"programlisting\"\u003eCREATE FOREIGN TABLE films (\n code char(5) NOT NULL,\n title text NOT NULL,\n rating text OPTIONS (force_null 'true')\n) SERVER film_server\nOPTIONS ( filename 'films/db.csv', format 'csv' );\n\u003c/pre\u003e\n\u003c/div\u003e\n\u003c/div\u003e\u003cbr class=\"example-break\"\u003e\n\u003c/div\u003e","related":[{"label":"Foreign-data wrapper catalog","url":"/wiki/catalog/pg_foreign_data_wrapper/?v=18"},{"label":"Existing extension catalogue identity","url":"/e/file_fdw/"},{"label":"Foreign Scan plan node","url":"/wiki/plan/foreign-scan/?v=18"}],"sections":[{"paragraphs":["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."],"title":"Interface and capability boundaries"},{"paragraphs":["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."],"title":"File access boundary"},{"code":"{\n\tFdwRoutine *fdwroutine = makeNode(FdwRoutine);\n\n\tfdwroutine-\u003eGetForeignRelSize = fileGetForeignRelSize;\n\tfdwroutine-\u003eGetForeignPaths = fileGetForeignPaths;\n\tfdwroutine-\u003eGetForeignPlan = fileGetForeignPlan;\n\tfdwroutine-\u003eExplainForeignScan = fileExplainForeignScan;\n\tfdwroutine-\u003eBeginForeignScan = fileBeginForeignScan;\n\tfdwroutine-\u003eIterateForeignScan = fileIterateForeignScan;\n\tfdwroutine-\u003eReScanForeignScan = fileReScanForeignScan;\n\tfdwroutine-\u003eEndForeignScan = fileEndForeignScan;\n\tfdwroutine-\u003eAnalyzeForeignTable = fileAnalyzeForeignTable;\n\tfdwroutine-\u003eIsForeignScanParallelSafe = fileIsForeignScanParallelSafe;\n\n\tPG_RETURN_POINTER(fdwroutine);\n}","title":"Registered implementation in core source"}],"tables":[{"columns":[{"key":"feature","label":"Interface operation"},{"key":"state","label":"Source observation"},{"key":"callback","label":"Callback"},{"key":"implementation","label":"Implementation"}],"key":"callbacks","rows":[{"callback":"IterateForeignScan","feature":"Scan rows","implementation":"fileIterateForeignScan","state":"Handler registered; conditions apply"},{"callback":"ExecForeignInsert","feature":"INSERT","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ExecForeignUpdate","feature":"UPDATE","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ExecForeignDelete","feature":"DELETE","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ExecForeignTruncate","feature":"TRUNCATE","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"GetForeignJoinPaths","feature":"Join path pushdown","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"GetForeignUpperPaths","feature":"Upper paths, including aggregation","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ExecForeignBatchInsert","feature":"Batch insert","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ForeignAsyncRequest","feature":"Asynchronous append execution","implementation":"Not registered","state":"No handler registered in this build"},{"callback":"ImportForeignSchema","feature":"Import schema","implementation":"Not registered","state":"No handler registered in this build"}],"title":"Registered interface handlers"},{"columns":[{"key":"name","label":"Option"},{"key":"definition","label":"Same-version definition"}],"key":"options","rows":[{"definition":"Specifies the file to be read. Relative paths are relative to the data directory. Either filename or program must be specified, but not both.","name":"filename"},{"definition":"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.","name":"program"},{"definition":"Specifies the data format, the same as COPY 's FORMAT option.","name":"format"},{"definition":"Specifies whether the data has a header line, the same as COPY 's HEADER option.","name":"header"},{"definition":"Specifies the data delimiter character, the same as COPY 's DELIMITER option.","name":"delimiter"},{"definition":"Specifies the data quote character, the same as COPY 's QUOTE option.","name":"quote"},{"definition":"Specifies the data escape character, the same as COPY 's ESCAPE option.","name":"escape"},{"definition":"Specifies the data null string, the same as COPY 's NULL option.","name":"null"},{"definition":"Specifies the string that represents a default value, the same as COPY 's DEFAULT option.","name":"default"},{"definition":"Specifies the data encoding, the same as COPY 's ENCODING option.","name":"encoding"},{"definition":"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.","name":"on_error"},{"definition":"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.","name":"reject_limit"},{"definition":"Specifies the amount of messages emitted by file_fdw , the same as COPY 's LOG_VERBOSITY option.","name":"log_verbosity"},{"definition":"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.","name":"force_not_null"},{"definition":"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.","name":"force_null"}],"title":"Documented options"}]}},"RequestedLocale":"zh-Hans","Fallback":true,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}