Foreign table
A 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 INSERT
, UPDATE
and DELETE
commands. There will however be a trade-off between the flexibilty provided by foreign tables and performance compared to native PostgreSQL tables.
Foreign tables
have been available since PostgreSQL 9.1.
Creating a foreign table
Before a 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 FDW
.
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
tuple routing
- there is no validation that all the rows in the
foreign table
obey the partition constraint - it is not possible to create/attach the
foreign table
as 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 table
partition, but they cannot be moved from a foreign table partition to another partition
Dumping foreign table data
Beginning with PostgreSQL 13, pg_dump
can optionally dump data from foreign tables with the --include-foreign-data
option.
Limitations
In PostgreSQL 13 and earlier, it was not possible to use the TRUNCATE
command to remove data from foreign tables.
Examples
Using 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')
References
- PostgreSQL documentation: Foreign data