Contents
Foreign Data Wrapper (FDW)
A 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 contrib modules:
postgres_fdw- enables read/write connections to other PostgreSQL databasesfile_fdw- enables reading from flat files
A large number of third-party foreign data wrappers are also available.
Implementation
A 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.
The PostgreSQL planner will then, when encountering queries which reference foreign tables, hand off the appropriate parts of the query to the respective foreign data wrapper.
History
Initial implementation of foreign data wrapper support commenced in PostgreSQL 8.4 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 14
- PostgreSQL 12
- support for generated columns added (commit fc22b662)
- PostgreSQL 11
INSERT,UPDATE, andCOPYon 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
- IMPORT FOREIGN SCHEMA support added (initial commit 59efda3e)
- CHECK constraints can be placed on foreign tables (commit fc2ac1fb)
- foreign tables can participate in inheritance (initial commit cb1ca4d8)
- foreign data wrappers can implement join pushdown (initial commit 1a8a4e5c)
- PostgreSQL 9.4
- trigger support added for foreign tables (commit 7cbe57c3)
- PostgreSQL 9.3
- writeable foreign table support added (initial commit dc3eb563)
- postgres_fdw added as a contrib module (initial commit d0d75c40)
- dblink modified to use foreign data wrapper functionality (commit 8255566f)
- 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 forALTER FOREIGN DATA WRAPPERandALTER SERVER(commit 5bcf8ede)CREATE TABLE (LIKE ...)able to use foreign table definitions (commit a9f2e31c)
- PostgreSQL 9.1
- read-only foreign table support added (initial commit 0d692a0d)
- file_fdw added as a contrib module (commit 7c5d0ae7)
- PostgreSQL 8.4
- initial internal implementation (commit cae565e5(
References
- PostgreSQL documentation: Foreign Data
- PostgreSQL documentation: Writing a Foreign Data Wrapper