Contents

file_fdw

A foreign data wrapper for querying files on the server's filesystem

file_fdw is a contrib module providing a foreign data wrapper (FDW) which enables files on the server's local filesystem to be queried as tables. The files must be in one of the formats supported by the COPY ... FROM command (text, csv or binary). file_fdw is read-only.

file_fdw was added in PostgreSQL 9.1.

Installation

The file_fdw extension must be installed by a superuser using the CREATE EXTENSION syntax into the database where its functionality is required.

Permissions

Prior to PostgreSQL 11, only a superuser could use file_fdw to read files from the operating system. From PostgreSQL 11, the following default roles are available to enable file_fdw usage by non-superusers:

  • pg_read_server_files
  • pg_write_server_files
  • pg_execute_server_program

Change history

Examples

As a contrived example, save the below sample CSV file as /tmp/file_fdw-test.csv:

ID,Message,Random numbers
1,Hello world,34432532
2,Goodbye cruel world,97325644

To query this file, set up a foreign table associated with the previously created foreign server and which contains columns defined to match those of the CSV file. The foreign table definition also contains the location of the file (which must readable by the database's system user). Note that the foreign table definition must be carried out as a superuser, as the source file will be read with the privileges of the database's system user, which is a potential security risk.

A foreign table suitable for reading the example file is:

CREATE EXTENSION file_fdw;

CREATE SERVER file_fdw_test_srv FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE csv_test (
 id INT,
 message TEXT,
 randnrs BIGINT
)
SERVER file_fdw_test_srv
OPTIONS (
   filename '/tmp/file_fdw-test.csv',
   format 'csv',
   header 'TRUE'
)

The file can then be queried as a normal table:

postgres=# SELECT * FROM csv_test;
 id |       message       | randnrs  
----+---------------------+----------
  1 | Hello world         | 34432532
  2 | Goodbye cruel world | 97325644

(2 rows)

Categories

Contrib module, Data import/export, Extension, Foreign Data Wrapper (FDW)

See also

COPY, CSV