Foreign Data Wrapper (FDW)
Foreign Data Wrapper (
FDW) is a PostgreSQL
extension enabling external data sources to be queried and written to using normal SQL. External data sources can be anything which provides access to some kind of data, including other databases (both PostgreSQL and other database engines), flat files, websites with data APIs, source code repositories and many other possibilities.
PostgreSQL provides two foreign data wrappers as
postgres_fdw- enables read/write connections to other PostgreSQL databases
file_fdw- enables reading from flat files
A large number of third-party foreign data wrappers are also available.
foreign data wrapper is implemented as an
extension which provides an interface between PostgreSQL and the external data source. The data source itself is defined by creating a
foreign server containing connection details and other options for the remote data source.
Foreign tables can then be created, defining the remote data in terms of PostgreSQL relations.
planner will then, when encountering queries which reference foreign tables, hand off the appropriate parts of the query to the respective foreign data wrapper.
Initial implementation of foreign data wrapper support commenced in PostgreSQL 8.4 (commit cae565e5) with the addtion of support for some elements of the
SQL/MED standard, though no user-accessible functionality became available until PostgreSQL 9.1.
The following list provides an overview of significant foreign data wrapper functionality added in PostgreSQL 9.1 and later; see the release notes for each PostgreSQL version for full details.
- PostgreSQL 12
- PostgreSQL 11
COPYon partitioned tables can now properly route rows to foreign partitions (commit 3d956d95)
- PostgreSQL 10
- support for pushing aggregate functions to the remote server (commit 7012b132)
- PostgreSQL 9.6
- PostgreSQL 9.5
- PostgreSQL 9.4
- PostgreSQL 9.3
- PostgreSQL 9.2
- planning API improvements providing more flexibility in join planning (commit b1495393)
- collection of statistics for foreign tables enabled (commit 263d9de6)
- per-column options for foreign tables added (initial commit 9088d1b9)
RENAMEoption added for
ALTER FOREIGN DATA WRAPPERand
ALTER SERVER(commit 5bcf8ede)
CREATE TABLE (LIKE ...)able to use foreign table definitions (commit a9f2e31c)
- PostgreSQL 9.1