foreign table is a database object which represents a table present on an external data source (which could be another PostgreSQL node or a completely different system) which is accessed by a
foreign data wrapper (FDW).
Foreign tables can be queried in exactly the same way as normal tables, and depending on the FDW implementation can be modified with
DELETE commands. There will however be a trade-off between the flexibility provided by foreign tables and performance compared to native PostgreSQL tables.
Foreign tables have been available since PostgreSQL 9.1.
Creating a foreign table
foreign table can be created, the appropriate
foreign data wrapper must be installed, and a
foreign server created which defines the connection to the remote data source. Usually a
user mapping is also required, which permits the local PostgreSQL user to access the remote data source.
When defining a
foreign table, table and column level options can be provided which typically enable mapping of the table/column names in PostgreSQL to those on the remote data source, and depending on the FDW implementation other settings can also be specified, such as whether the remote table can be updated.
Foreign tables can also be automatically imported from a remote data source using the
IMPORT FOREIGN SCHEMA command, though availablity of this depends on the suitability of the remote data source for such operations (for example it would be infeasible for the file-based
file_fdw to extract a schema from a file) and the implementation provided by the respective
Foreign tables as partitions
Beginning with PostgreSQL 11,
foreign tables can be attached as partition tables, albeit with certain limitations/restrictions:
- the foreign data wrapper must support
- there is no validation that all the rows in the
foreign tableobey the partition constraint
- it is not possible to create/attach the
foreign tableas a partition of the parent table if there are any unique indexes on the parent table
- rows can be moved from local partitions to a
foreign tablepartition, but they cannot be moved from a foreign table partition to another partition
Dumping foreign table data
postgres_fdw to create a simple "loopback" foreign table which actually references a table on the same server:
postgres=# CREATE EXTENSION postgres_fdw; CREATE EXTENSION postgres=# CREATE SERVER fdw_test FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host 'localhost', port '5432', dbname 'postgres' ); postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER fdw_test OPTIONS(user 'postgres'); CREATE USER MAPPING postgres=# CREATE FOREIGN TABLE bar ( f_id INT OPTIONS (column_name 'id') NOT NULL, f_val TEXT OPTIONS (column_name 'val') ) SERVER fdw_test OPTIONS (table_name 'foo'); CREATE FOREIGN TABLE postgres=# \det List of foreign tables Schema | Table | Server --------+-------------+---------- public | bar | fdw_test (1 row) postgres=# \d bar Foreign table "public.bar" Column | Type | Collation | Nullable | Default | FDW options --------+---------+-----------+----------+---------+--------------------- f_id | integer | | not null | | (column_name 'id') f_val | text | | | | (column_name 'val') Server: fdw_test FDW options: (table_name 'foo')