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 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
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