Foreign table

A database object representing a table on an external data source

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.

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

Useful articles

Useful links

Contents