Foreign tables

Eugenia Kuzina

Overview

PostgreSQL partially implements the SQL/MED specification. It allows you to use regular SQL queries to access data that resides outside PostgreSQL. This data is called third-party data. To access it, follow the steps below:

  1. Use a foreign data wrapper. A foreign data wrapper connects to external data sources and obtains data from them. The contrib extension included in the ADPG installation contains multiple foreign data wrappers. See Additional Supplied Modules for details. You can also use third-party data wrappers or write your own. See Writing a foreign data wrapper.

  2. Define a foreign server object that encapsulates connection information for the corresponding foreign data wrapper. Use the CREATE SERVER command for this:

    CREATE SERVER [ IF NOT EXISTS ] <server_name> [ TYPE '<server_type>' ] [ VERSION '<server_version>' ]
FOREIGN DATA WRAPPER <fdw_name>
[ OPTIONS ( <option 'value' [, ... ]> ) ]
    Parameters of the CREATE SERVER command

    IF NOT EXISTS

    When this option is specified, PostgreSQL does not throw an error if a server with the specified name exists. There is no guarantee that the existing server matches the one that would be created

    <server_name>

    The name of a new foreign server. The server name must be unique within the database

    <server_type>

    An optional parameter that specifies a server type. It can be useful to foreign data wrappers

    <server_version>

    An optional parameter that specifies a server version. It can be useful to foreign data wrappers

    <fdw_name>

    A name of a foreign data wrapper that manages the foreign server

    OPTIONS ( <option 'value' [, …​ ]> )

    Specifies the server options. These options define the connection details, but the names and values are dependent on the specified foreign data wrapper

    The user, who defines the server, becomes its owner. To create the server, the user should have the USAGE privilege on the specified foreign data wrapper.

  3. Access to remote data may require authentication to an external data source. Create a user mapping object to specify a user name and password that can be used for the authentication of a current PostgreSQL role. To do this, execute the CREATE USER MAPPING command that has the following syntax:

    CREATE USER MAPPING [ IF NOT EXISTS ] FOR { <user_name> | USER | CURRENT_ROLE | CURRENT_USER | PUBLIC }
    SERVER <server_name>
    [ OPTIONS ( <option 'value' [ , ... ]> ) ]
    Parameters of the CREATE USER MAPPING command

    IF NOT EXISTS

    Do not throw an error if a mapping of the given user to the given foreign server already exists. There is no guarantee that the existing user mapping matches the one that would be created

    <user_name>

    Specifies the name of an existing user that is mapped to a foreign server. CURRENT_ROLE, CURRENT_USER, and USER match the name of the current user. When PUBLIC is specified, the public mapping is created that is used when no user-specific mapping is applicable

    <server_name>

    Defines a name of an existing server for which the user mapping is created

    OPTIONS ( <option 'value' [, …​ ]> )

    Specifies the options for user mapping. The options typically define the actual remote user name and password. The allowed option names and values are specific to the used foreign data wrapper

    The owner of a foreign server can create user mappings for that server for any user. A user also can create a user mapping for their own user name if the USAGE privilege on the server is granted to the user.

  4. Create one or more foreign tables that define the remote data structure. A foreign table does not store data on the PostgreSQL server, but you can execute queries on it as on a regular table. PostgreSQL utilizes a foreign data wrapper to fetch data from an external source or transmit data to this source in case of update commands.

    Execute the CREATE FOREIGN TABLE command to define a foreign table:

    CREATE FOREIGN TABLE [ IF NOT EXISTS ] <table_name> ( [
  { <column_name> <data_type> [ COLLATE <collation> ] [ <column_constraint> [ ... ] ] | <table_constraint> }
    [, ... ]
] )
[ INHERITS ( <parent_table> [, ... ] ) ]
  SERVER <server_name>
[ OPTIONS ( <option 'value'> [, ... ] ) ]

CREATE FOREIGN TABLE [ IF NOT EXISTS ] <table_name>
  PARTITION OF <main_table> [ (
  { <column_name> [ WITH OPTIONS ] [ <column_constraint> [ ... ] ]
    | <table_constraint> }
    [, ... ]
) ]
{ FOR VALUES <partition_bound_spec> | DEFAULT }
  SERVER <server_name>
[ OPTIONS ( <option 'value' [, ... ]> ) ]
    Parameters of the CREATE FOREIGN TABLE command

    IF NOT EXISTS

    When this option is specified, PostgreSQL does not throw an error if a relation with the specified name is in the database. There is no guarantee that the existing relation matches the one that would be created

    <table_name>

    The name of a new table. Can be schema-qualified (<schema_name>.<table_name>)

    <column_name>

    The name of a column to be created in the new table

    <data_type>

    The column data type. This can include array specifiers. For more information on supported data types, refer to Data types

    COLLATE <collation>

    Defines the column collation. If this setting is not specified, PostgreSQL uses the column data type’s default collation

    INHERITS ( <parent_table> [, …​ ] )

    Specifies a list of tables from which the new table automatically inherits all columns. Parent tables can be ordinary tables or foreign tables. See Inheritance

    PARTITION OF <parent_table>

    Creates a foreign table as a partition of the given parent table with specified partition bound values. See Partitioning for more information on partitioning options. Note that it is not allowed to create the foreign table as a partition of the parent table if there are UNIQUE indexes on the parent table

    <column_constraint>

    Defines column constraints that are described in the Use constraints section

    <table_constraint>

    Specifies table constraints that are described in the Use constraints section

    <server_name>

    Defines the name of an existing foreign server to use for a foreign table

    OPTIONS ( <option 'value' [, …​]> )

    Specifies options associated with a new foreign table or one of its columns. The allowed option names and values are specific to each foreign data wrapper

    Alternatively, you can import the entire foreign schema. To do this, execute the IMPORT FOREIGN SCHEMA command. It creates foreign tables that represent tables existing on a foreign server. PostgreSQL creates foreign tables with column definitions and options that match the remote tables. The user, who executes the IMPORT FOREIGN SCHEMA command, is the owner of new foreign tables. The command has the following syntax:

    IMPORT FOREIGN SCHEMA <remote_schema>
    [ { LIMIT TO | EXCEPT } ( <table_name> [, ...] ) ]
    FROM SERVER <server_name>
    INTO <local_schema>
    [ OPTIONS ( <option 'value' [, ... ]> ) ]
    Parameters of the IMPORT FOREIGN SCHEMA command

    <remote_schema>

    Defines a remote schema to import from. What exactly a remote schema is depends on the used foreign data wrapper

    LIMIT TO ( <table_name [, …​]> )

    Imports only foreign tables matching one of the given table names. Other tables existing in the foreign schema are ignored

    EXCEPT ( <table_name [, …​]> )

    Excludes specified foreign tables from the import. All tables existing in the foreign schema are imported except the ones listed in this clause

    <server_name>

    Defines the foreign server to import from

    <local_schema>

    Sets the schema in which the imported foreign tables are created

    OPTIONS ( <option 'value' [, …​]> )

    Specifies options to be used during the import. The allowed option names and values are specific to each foreign data wrapper

    The list of imported tables can be limited to a specified subset, or specific tables can be excluded from import. The target local schema must already exist.

    To run IMPORT FOREIGN SCHEMA, the user must have the USAGE privilege on the foreign server and the CREATE privilege on the target local schema.

To display foreign tables from the current database, use the following query:

SELECT * FROM information_schema.foreign_tables;

Examples

The contrib extension contains the following data wrappers:

  • postgres_fdw — can be used to access data stored in external PostgreSQL servers. See postgres_fdw.

  • file_fdw — can be used to access data files in the server file system, or to execute programs on the server and read their output. See file_fdw.

You can find examples of using these data wrappers below.

Access data stored in external PostgreSQL servers

Assume that an external PostgreSQL server contains the books and authors tables in the books_store database.

The authors table is created as follows:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

To access data from this server, follow the steps below:

  1. Use an existing role on a remote server or create a new user and grant appropriate permissions. For example:

    CREATE ROLE user1 WITH PASSWORD 'password' LOGIN;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user1;

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user1;

  2. Since ADPG includes the contrib extension, you just need to execute the CREATE EXTENSION command to use the postgres_fdw data wrapper on the local server:

    CREATE EXTENSION postgres_fdw;

  3. Create a foreign server in your local database. The following code creates a server that connects to the books_store database located on the 10.92.6.225 host:

    CREATE SERVER server1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.92.6.225', port '5432', dbname 'books_store');

    If the remote server uses the standard port, the port option can be omitted.

  4. Define user mapping. Pass the credentials of the user you created on the remote server as options. The following code creates user mapping for the postgres user from the local server:

    CREATE USER MAPPING FOR postgres SERVER server1 OPTIONS (user 'user1', password 'password');

  5. Create a foreign table on the local server:

    CREATE FOREIGN TABLE authors (
    id int,
    name VARCHAR(100) NOT NULL
)
SERVER server1;

    Check the result:

    SELECT * FROM authors;

    The output:

     id |        name
----+---------------------
  1 | Virginia Woolf
  2 | Harper Lee
  3 | F. Scott Fitzgerald
  4 | J.R.R. Tolkien
  5 | George Orwell

    Alternatively, import the entire scheme:

    CREATE SCHEMA schema1;
IMPORT FOREIGN SCHEMA public from SERVER server1 into schema1;

    Display the existing foreign tables to check the result:

    SELECT * FROM information_schema.foreign_tables;

    The result:

     foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+---------------------
 postgres              | public               | authors            | postgres               | server1
 postgres              | schema1              | authors            | postgres               | server1
 postgres              | schema1              | books              | postgres               | server1

The books and authors tables have been imported into shema1.

Access data files in the server file system

For example, we have the following books.csv file:

1,Mrs. Dalloway,1,1925,novel
2,To the Lighthouse,1,1927,novel
3,To Kill a Mockingbird,2,1960,novel
4,The Lord of the Rings,4,1955,fantasy
5,1984,5,1949,sci-fi

We need to use SQL to access the data from this file. To accomplish this task, perform the following steps:

  1. Execute the CREATE EXTENSION command to use the file_fdw data wrapper:

    CREATE EXTENSION file_fdw;

  2. Define a foreign server:

    CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;

  3. Create a foreign table. Its structure should match the file structure. Pass the file path and file format in the OPTIONS clause:

    CREATE FOREIGN TABLE foreign_books (
    id INT,
    title VARCHAR(255),
    author_id INT,
    public_year INT,
    genre VARCHAR(50)
) SERVER file_server
OPTIONS ( filename '/var/lib/pgsql/books.csv', format 'csv' );

Now you can use SQL commands to access data, for example:

SELECT * FROM foreign_books;

The result:

 id |         title         | author_id | public_year |  genre
----+-----------------------+-----------+-------------+---------
  1 | Mrs. Dalloway         |         1 |        1925 | novel
  2 | To the Lighthouse     |         1 |        1927 | novel
  3 | To Kill a Mockingbird |         2 |        1960 | novel
  4 | The Lord of the Rings |         4 |        1955 | fantasy
  5 | 1984                  |         5 |        1949 | sci-fi
Found a mistake? Seleсt text and press Ctrl+Enter to report it