IMPORT FOREIGN SCHEMA v17

Suggest edits

Name

IMPORT FOREIGN SCHEMA — Import a foreign schema from a foreign server or database link.

Synopsis and parameters

See IMPORT FOREIGN SCHEMA for command syntax, parameters and functionality.

NOT NULL constraints are imported by default. Use the import_not_null option if you don’t want to import NOT NULL constraints.

Description

After you create an access to a remote database with either a Foreign Data Wrapper or a database link, you can import a foreign schema with IMPORT FOREIGN SCHEMA. You can import foreign schemas with database links created with edb_dblink_oci connections as explained in the database link creation examples.

You can then access the imported schema objects locally, instead of remotely.

Example

This example uses the IMPORT FOREIGN SCHEMA command to import a database schema named IMP from an Oracle server to an EDB Postgres Advanced Server. The target schema with the imported table definitions is called tmp_schema and uses an edb_dblink_oci connection called oci_link.

Prerequisites

Import schemas

  1. Create a server (here called oci_link) that connects you to the Oracle database. Specify the foreign data wrapper, and the connection string required to reach the Oracle service.

    CREATE SERVER oci_link FOREIGN DATA WRAPPER oci_dblink OPTIONS (connstr :ORACLE_SERVICE);

    Where ORACLE_SERVICE is the environment variable for the name of the Oracle service from which you want to do the import.

  2. Map the EDB Postgres edb user to the Oracle database user for which you want to import the schemas. Use the oci_link connection created previously, and specify the Oracle’s user and password:

    CREATE USER MAPPING FOR edb SERVER oci_link OPTIONS ("user" 'a', password 'c');

    Where 'a' is the Oracle user name that has access to the foreign schema and 'c', its password.

  3. Create a schema where you’ll place the imported objects:

    CREATE SCHEMA tmp_schema;

  4. Import the IMP schema from the source database to EDB Postgres Advanced tmp_schema using the oci_link connection.

    IMPORT FOREIGN SCHEMA "IMP" FROM SERVER oci_link INTO tmp_schema;

    This command creates foreign tables that represent the tables existing on the Oracle server.

    By default, all tables and views existing in a particular schema on the foreign server are imported. Optionally, the list of tables can be limited to a specified subset, or specific tables can be excluded.

  5. Verify the imported foreign table contains all the schema objects you expect it to have:

    SELECT * FROM tmp_schema."FOO";
    Output
     a | b | c | d
---+--------+------------+-----
 1 | 1.1000 | ABC | XYZ
(1 row)
    SELECT * FROM tmp_schema."Foo";
    Output
     a | b | c | d
---+--------+------------+-----
 2 | 2.2000 | PQR | STU
(1 row)
    SELECT * FROM tmp_schema."V1";
    Output
     a | b | c | d
---+--------+------------+-----
 1 | 1.1000 | ABC | XYZ
(1 row)
← Prev

INSERT

↑ Up

Compatible SQL commands

Next →

MERGE