file_fdw
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
- PostgreSQL 10
file_fdw
can read output from programs as well as from files (commit 8e91e12b)
- PostgreSQL 9.4
force_null
option added (commit 3b5e03dc)
- PostgreSQL 9.2
force_not_null
option added (commit 86a3f2d4)
- PostgreSQL 9.1
- added (commit 7c5d0ae7)
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)
References
- PostgreSQL documentation: file_fdw
- PostgreSQL documentation: COPY command