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 (
file_fdw is read-only.
file_fdw was added in PostgreSQL 9.1.
file_fdw extension must be installed by a superuser using the
CREATE EXTENSION syntax into the database where its functionality is required.
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:
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)