CREATE PUBLIC DATABASE LINK v17

Name

CREATE [PUBLIC] DATABASE LINK — Create a database link.

Synopsis

CREATE [ PUBLIC ] DATABASE LINK <name>
  CONNECT TO { CURRENT_USER |
               <username> IDENTIFIED BY '<password>'}
  USING { postgres_fdw '<fdw_connection_string>' |
        [ oci ] '<oracle_connection_string>' }

Description

CREATE DATABASE LINK creates a database link. A database link is an object that allows a reference to a table or view in a remote database in a DELETE, INSERT, SELECT or UPDATE command. Reference a database link by appending @dblink to the table or view name referenced in the SQL command, where dblink is the name of the database link.

Database links can be public or private. A public database link is one that any user can use. Only the database link's owner can use a private database link. Specify the PUBLIC option to create a public database link. Otherwise, a private database link is created.

When you use the CREATE DATABASE LINK command, the database link name and the given connection attributes are stored in the EDB Postgres Advanced Server system table named pg_catalog.edb_dblink. When using a given database link, the database containing the edb_dblink entry defining this database link is called the local database. The server and database whose connection attributes are defined in the edb_dblink entry is called the remote database. You can use edb_dblink_oci to access remote Oracle tables and views using any SELECT, INSERT, UPDATE, or DELETE statement.

You must be connected to the local database when you issue a SQL command containing a reference to a database link. When the SQL command executes, the appropriate authentication and connection is made to the remote database to access the table or view to which the @dblink reference is appended.

The edb_dblink_oci supports both types of rescans: SCROLL and SERIALIZABLE. By default it's set to SERIALIZABLE. When set to SERIALIZABLE, edb_dblink_oci uses the SERIALIZABLE transaction isolation level on the Oracle side, which corresponds to PostgreSQL’s REPEATABLE READ. This is necessary because a single PostgreSQL statement can lead to multiple Oracle queries. It thereby uses a serializable isolation level to provide consistent results.

A serialization failure can occur when modifying a table concurrent with long-running DML transactions, for example, ADD, UPDATE, or DELETE statements. If such a failure occurs, the OCI reports ORA-08177: can't serialize access for this transaction, and the application must retry the transaction.

A SCROLL rescan is quick, but each iteration resets the current row position to 1. A SERIALIZABLE rescan has performance benefits over a SCROLL rescan.

Parameters

PUBLIC

Create a public database link that any user can use. If you omit this parameter, then the database link is private and only the database link’s owner can use it.

name

The name of the database link.

username

The username to use for connecting to the remote database.

CURRENT_USER

Include CURRENT_USER to use the user mapping associated with the role that's using the link when establishing a connection to the remote server.

password

The password for username.

postgres_fdw

Specifies foreign data wrapper postgres_fdw as the connection to a remote EDB Postgres Advanced Server database. If postgres_fdw isn't installed on the database, use the CREATE EXTENSION command to install postgres_fdw. For more information, see the CREATE EXTENSION command in the PostgreSQL core documentation.

fdw_connection_string

Specifies the connection information for the postgres_fdw foreign data wrapper.

oci

Specifies a connection to a remote Oracle database. This is the default behavior.

oracle_connection_string

Specifies the connection information for an oci connection.

Setting up an Oracle instant client for OCI database link

To use edb_dblink_oci, you must download and install an Oracle instant client on the host running the EDB Postgres Advanced Server database in which you want to create the database link.

You can download an instant client here.

Oracle instant client for Linux

These instructions apply to Linux hosts running EDB Postgres Advanced Server.

Be sure the libaio library (the Linux-native asynchronous I/O facility) is installed on the Linux host running EDB Postgres Advanced Server.

You can install the libaio library with the following command:

yum install libaio

If the Oracle instant client that you downloaded doesn't include the file named libclntsh.so without a version number suffix, create a symbolic link named libclntsh.so that points to the downloaded version of the library file. To do so, navigate to the instant client directory and execute the following command:

ln -s libclntsh.so.<version> libclntsh.so

Where version is the version number of the libclntsh.so library. For example:

ln -s libclntsh.so.12.1 libclntsh.so

When executing a SQL command that references a database link to a remote Oracle database, EDB Postgres Advanced Server must know where the Oracle instant client library resides on the EDB Postgres Advanced Server host.

The LD_LIBRARY_PATH environment variable must include the path to the Oracle client installation directory containing the libclntsh.so file. For example, if the installation directory containing libclntsh.so is /tmp/instantclient, use:

export LD_LIBRARY_PATH=/tmp/instantclient:$LD_LIBRARY_PATH

Alternatively, you can set the value of the oracle_home configuration parameter in the postgresql.conf file. The oracle_home configuration parameter is an alternative to the LD_LIBRARY_PATH environment variable. For more details on the oracle_home configuration parameter, see configuration parameters.

The ORACLE_HOME environment variable must be set and include the path to the Oracle home directory. For example,

export ORACLE_HOME=/opt/product/version/dbhomeXE

If you're running the current session as the user account (for example, enterprisedb) that directly invokes pg_ctl to start or restart EDB Postgres Advanced Server, then set either the LD_LIBRARY_PATH environment variable or the oracle_home configuration parameter and the ORACLE_HOME environment variable before invoking pg_ctl.

You can set the LD_LIBRARY_PATH and the ORACLE_HOME environment variable in the .bash_profile file under the home directory of the enterprisedb user account. That is, set LD_LIBRARY_PATH and ORACLE_HOME in the file ~enterprisedb/.bash_profile. This setting ensures that LD_LIBRARY_PATH and ORACLE_HOME are set when you log in as enterprisedb.

If you're using a Linux service script with the systemctl or service command to start or restart EDB Postgres Advanced Server, you must set LD_LIBRARY_PATH and ORACLE_HOME so it's in effect when the script invokes the pg_ctl utility.

For example, to set an environment variable for EDB Postgres Advanced Server, you can create a file named /etc/systemd/system/edb-as-14.service. Include /lib/systemd/system/edb-as-14.service in the file.

If the LD_LIBRARY_PATH=/tmp/instantclient, include the environment variable by specifying:

[Service]
Environment=LD_LIBRARY_PATH=/tmp/instantclient:$LD_LIBRARY_PATH
Environment=ORACLE_HOME=/tmp/instantclient

Reload systemd:

systemctl daemon-reload

Restart the EDB Postgres Advanced Server service:

systemctl restart edb-as-14

The script file that you need to modify to include the LD_LIBRARY_PATH setting depends on the EDB Postgres Advanced Server version and the Linux system on which it was installed.

Oracle instant client for Windows

These instructions apply to Windows hosts running EDB Postgres Advanced Server.

When you're executing a SQL command that references a database link to a remote Oracle database, EDB Postgres Advanced Server must know where the Oracle instant client library resides on the EDB Postgres Advanced Server host.

Set the Windows PATH system environment variable to include the Oracle client installation directory that contains the oci.dll file.

Alternatively, you can set the value of the oracle_home configuration parameter in the postgresql.conf file. The value specified in the oracle_home configuration parameter overrides the Windows PATH environment variable.

To set the oracle_home configuration parameter in the postgresql.conf file, add the following line:

oracle_home = 'lib_directory'

Substitute the name of the Windows directory that contains oci.dll for lib_directory. For example:

oracle_home = 'C:/tmp/instantclient_10_2'

After setting the PATH environment variable or the oracle_home configuration parameter, you must restart the server for the changes to take effect. Restart the server from the Windows Services console.

Examples

Creating an OCI database link

This example uses the CREATE DATABASE LINK command to create a database link named chicago that connects an instance of EDB Postgres Advanced Server to an Oracle server using an edb_dblink_oci connection. The connection information tells EDB Postgres Advanced Server to log in to Oracle as the user admin whose password is mypassword. Including the oci option tells EDB Postgres Advanced Server that this is an edb_dblink_oci connection. The connection string '//127.0.0.1/acctg' specifies the server address and name of the database.

CREATE DATABASE LINK chicago
  CONNECT TO admin IDENTIFIED BY 'mypassword'
  USING oci '//127.0.0.1/acctg';

Creating a postgres_fdw database link

This example uses the CREATE DATABASE LINK command to create a database link named bedford. The database link connects an instance of EDB Postgres Advanced Server to another EDB Postgres Advanced Server instance by way of a postgres_fdw foreign data wrapper connection. The connection information tells EDB Postgres Advanced Server to log in as the user admin with the password mypassword. Including the postgres_fdw option tells EDB Postgres Advanced Server that this is a postgres_fdw connection. The connection string, 'host=127.0.0.1 port=5444 dbname=marketing' specifies the server address and name of the database.

CREATE DATABASE LINK bedford
  CONNECT TO admin IDENTIFIED BY 'mypassword'
  USING postgres_fdw 'host=127.0.0.1 port=5444 dbname=marketing';

Managing user mappings and passwords for database links

When you create a database link using either OCI (with dblink_ora) or postgres_fdw, the password for the remote server connection is securely stored in the local database's pg_user_mapping system table. To keep it safe, the password is saved in an obfuscated format so it can't be easily read.

This obfuscation is linked to the local Postgres username for whom the USER MAPPING is created. Therefore, changing/dropping only this user (for example, using ALTER USER MAPPING ... OPTIONS (set/drop user)) invalidates the existing obfuscated password stored in pg_user_mapping for that database link, which may result in an inconsistent state or connection failures.

To avoid issues and maintain the connection, if you need to change the username associated with an existing database link:

• Change both the username and password, instead of changing the username only:

  → WrapCopy

  ALTER USER MAPPING FOR edb SERVER oracle_server OPTIONS (set user 'new_user', set password 'new_password');

• Alternatively, you can drop the existing username/password combination, and add a new username/password combination:

  → WrapCopy

  ALTER USER MAPPING FOR edb SERVER oracle_server OPTIONS (DROP 'user'. DROP 'password');
  ALTER USER MAPPING FOR edb SERVER oracle_server OPTIONS (ADD USER 'new_user', ADD password 'new_password');

Using a database link

These examples use a database link with EDB Postgres Advanced Server to connect to an Oracle database. The examples assume that a copy of the EDB Postgres Advanced Server sample application’s emp table was created in an Oracle database. A second EDB Postgres Advanced Server database cluster with the sample application is accepting connections at port 5443.

Create a public database link, named oralink, to an Oracle database named xe located at 127.0.0.1 on port 1521. Connect to the Oracle database with the username edb and password password.

CREATE PUBLIC DATABASE LINK oralink CONNECT TO edb IDENTIFIED BY 'password'
USING '//127.0.0.1:1521/xe';

Issue a SELECT command on the emp table in the Oracle database using the database link oralink.

SELECT * FROM emp@oralink;

 empno | ename  |    job    | mgr  |      hiredate      | sal  | comm | deptno
-------+--------+-----------+------+--------------------+------+------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800 |      |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600 |  300 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250 |  500 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975 |      |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250 | 1400 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850 |      |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450 |      |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000 |      |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000 |      |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500 |    0 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100 |      |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950 |      |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000 |      |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300 |      |     10
(14 rows)

Create a private database link, named fdwlink, to the EDB Postgres Advanced Server database named edb located on host 192.168.2.22 running on port 5444. Connect to the EDB Postgres Advanced Server database with the username enterprisedb and password password.

CREATE DATABASE LINK fdwlink CONNECT TO enterprisedb IDENTIFIED BY
'password' USING postgres_fdw 'host=192.168.2.22 port=5444 dbname=edb';

Display attributes of database links oralink and fdwlink from the local edb_dblink system table:

SELECT lnkname, lnkuser, lnkconnstr FROM pg_catalog.edb_dblink;

 lnkname |   lnkuser    |               lnkconnstr
---------+--------------+----------------------------------------
 oralink | edb          | //127.0.0.1:1521/xe
 fdwlink | enterprisedb |
(2 rows)

Perform a join of the emp table from the Oracle database with the dept table from the EDB Postgres Advanced Server database:

SELECT d.deptno, d.dname, e.empno, e.ename, e.job, e.sal, e.comm FROM
emp@oralink e, dept@fdwlink d WHERE e.deptno = d.deptno ORDER BY 1, 3;

 deptno |   dname    | empno | ename  |    job    | sal  | comm
--------+------------+-------+--------+-----------+------+------
     10 | ACCOUNTING |  7782 | CLARK  | MANAGER   | 2450 |
     10 | ACCOUNTING |  7839 | KING   | PRESIDENT | 5000 |
     10 | ACCOUNTING |  7934 | MILLER | CLERK     | 1300 |
     20 | RESEARCH   |  7369 | SMITH  | CLERK     |  800 |
     20 | RESEARCH   |  7566 | JONES  | MANAGER   | 2975 |
     20 | RESEARCH   |  7788 | SCOTT  | ANALYST   | 3000 |
     20 | RESEARCH   |  7876 | ADAMS  | CLERK     | 1100 |
     20 | RESEARCH   |  7902 | FORD   | ANALYST   | 3000 |
     30 | SALES      |  7499 | ALLEN  | SALESMAN  | 1600 |  300
     30 | SALES      |  7521 | WARD   | SALESMAN  | 1250 |  500
     30 | SALES      |  7654 | MARTIN | SALESMAN  | 1250 | 1400
     30 | SALES      |  7698 | BLAKE  | MANAGER   | 2850 |
     30 | SALES      |  7844 | TURNER | SALESMAN  | 1500 |    0
     30 | SALES      |  7900 | JAMES  | CLERK     |  950 |
(14 rows)

Push down for an OCI database link

When the OCI database link is used to execute SQL statements on a remote Oracle database, sometimes the pushdown of the processing occurs on the foreign server.

Push down refers to the occurrence of processing on the foreign that is, remote server instead of the local client where the SQL statement was issued. Push down can result in performance improvement since the data is processed on the remote server before being returned to the local client.

OCI database link supports push down for

• WHERE clause
• JOIN operations
• AGGREGATE functions
• ORDER BY clause

For push down to occur, some basic conditions must be met. The tables involved in the join operation must belong to the same foreign server and use the identical connection information to the foreign server. This connection information is the same database link defined with the CREATE DATABASE LINK command.

To determine whether to use push down for a SQL statement, display the execution plan by using the EXPLAIN command. For information about the EXPLAIN command, see the PostgreSQL core documentation.

You can restrict the push downs using the edb_dbLink_oci_pushdown.config configuration file. You can define the list of functions and operators in this file that can push down to the remote server. You can easily add or modify the list as per the requirements.

This file lists the objects as aggregates, functions, and operators allowed to push down to the remote server. Put each entry on a single line. Each entry must have two columns:

• Object type that can be ROUTINE (functions, aggregates, and procedures) or OPERATOR.
• The second column is schema-qualified object names with their arguments.

You can format the second column using the following query:

For ROUTINES:

SELECT pronamespace::regnamespace || '.' || oid::regprocedure FROM pg_proc
WHERE proname = '<routine_name>'

For OPERATORS:

SELECT oprnamespace::regnamespace || '.' || oid::regoperator FROM pg_operator
WHERE oprname = '<operator_name>'

Example of edb_dbLink_oci_pushdown.config file:

ROUTINE pg_catalog.sum(bigint)
ROUTINE pg_catalog.sum(smallint)
ROUTINE pg_catalog.to_number(text)
ROUTINE pg_catalog.to_number(text,text)
OPERATOR pg_catalog.=(integer,integer)
OPERATOR pg_catalog.=(text,text)
OPERATOR pg_catalog.=(smallint,integer)
OPERATOR pg_catalog.=(bigint,integer)
OPERATOR pg_catalog.=(numeric,numeric)

To find out whether pushdown is used for a SQL statement, display the execution plan by using the EXPLAIN command.

These examples use the following database link:

CREATE PUBLIC DATABASE LINK oralink CONNECT TO edb IDENTIFIED BY 'password'
USING '//192.168.2.23:1521/xe';

This example shows the execution plan of an inner join:

EXPLAIN (verbose,costs off) SELECT d.deptno, d.dname, e.empno, e.ename FROM
dept@oralink d, emp@oralink e WHERE d.deptno = e.deptno ORDER BY 1, 3;

                                        QUERY PLAN
--------------------------------------------------------------------------------
 Foreign Scan
   Output: d.deptno, d.dname, e.empno, e.ename
   Relations: (_dblink_dept_1 d) INNER JOIN (_dblink_emp_2 e)
   Remote Query: SELECT r1.deptno, r1.dname, r2.empno, r2.ename FROM (dept r1 INNER 
JOIN emp r2 ON ((r1.deptno = r2.deptno))) ORDER BY r1.deptno ASC NULLS LAST, r2.empno ASC NULLS LAST
(4 rows)
# The `INNER JOIN` operation occurs under the Foreign Scan section. The output of this join is the following:

deptno |   dname    | empno | ename
-------+------------+-------+--------
    10 | ACCOUNTING |  7782 | CLARK
    10 | ACCOUNTING |  7839 | KING
    10 | ACCOUNTING |  7934 | MILLER
    20 | RESEARCH   |  7369 | SMITH
    20 | RESEARCH   |  7566 | JONES
    20 | RESEARCH   |  7788 | SCOTT
    20 | RESEARCH   |  7876 | ADAMS
    20 | RESEARCH   |  7902 | FORD
    30 | SALES      |  7499 | ALLEN
    30 | SALES      |  7521 | WARD
    30 | SALES      |  7654 | MARTIN
    30 | SALES      |  7698 | BLAKE
    30 | SALES      |  7844 | TURNER
    30 | SALES      |  7900 | JAMES
(14 rows)

This example shows the execution plan of an left outer join:

EXPLAIN (verbose,costs off) SELECT d.deptno, d.dname, e.empno, e.ename FROM
dept@oralink d LEFT OUTER JOIN emp@oralink e ON d.deptno = e.deptno ORDER BY 1, 3;

                                        QUERY PLAN
--------------------------------------------------------------------------------
 Foreign Scan
   Output: d.deptno, d.dname, e.empno, e.ename
   Relations: (_dblink_dept_1 d) LEFT JOIN (_dblink_emp_2 e)
   Remote Query: SELECT r1.deptno, r1.dname, r2.empno, r2.ename FROM (dept r1 LEFT JOIN 
emp r2 ON ((r1.deptno = r2.deptno))) ORDER BY r1.deptno ASC NULLS LAST, r2.empno ASC NULLS LAST
(4 rows)
# The output of this join is the following:

deptno |   dname    | empno | ename
-------+------------+-------+--------
    10 | ACCOUNTING |  7782 | CLARK
    10 | ACCOUNTING |  7839 | KING
    10 | ACCOUNTING |  7934 | MILLER
    20 | RESEARCH   |  7369 | SMITH
    20 | RESEARCH   |  7566 | JONES
    20 | RESEARCH   |  7788 | SCOTT
    20 | RESEARCH   |  7876 | ADAMS
    20 | RESEARCH   |  7902 | FORD
    30 | SALES      |  7499 | ALLEN
    30 | SALES      |  7521 | WARD
    30 | SALES      |  7654 | MARTIN
    30 | SALES      |  7698 | BLAKE
    30 | SALES      |  7844 | TURNER
    30 | SALES      |  7900 | JAMES
    40 | OPERATIONS |       |
(15 rows)

This example shows the execution plan of ORDER BY clause:

EXPLAIN (verbose, costs off)
 SELECT department_id, COUNT(*), SUM(salary), AVG(salary), MIN(salary), MAX(salary), ROUND(STDDEV(salary)), SUM(salary) * aggpd_pkg.pkgvar as sum2 
 FROM employees@oralink
 WHERE department_id < 50 
 GROUP BY department_id 
 ORDER BY 1;    
                                              QUERY PLAN                                                                                                           
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: department_id, (count(*)), (sum(salary)), (avg(salary)), (min(salary)), (max(salary)), (round(stddev(salary), 0)), ((sum(salary)) * (public.aggpd_pkg.pkgvar)::numeric)
   Relations: Aggregate on (_dblink_employees_1 employees)
   Remote Query: SELECT department_id, count(*), sum(salary), avg(salary), min(salary), max(salary), round(stddev(salary), 0) FROM employees WHERE (department_id < 50) GROUP BY department_id ORDER BY department_id ASC NULLS LAST
(4 rows)

This example shows a case where the entire processing isn't pushed down because the emp joined table resides locally instead of on the same foreign server:

EXPLAIN (verbose,costs off) SELECT d.deptno, d.dname, e.empno, e.ename FROM
dept@oralink d LEFT OUTER JOIN emp e ON d.deptno = e.deptno ORDER BY 1, 3;

                            QUERY PLAN
------------------------------------------------------------------
 Sort
   Output: d.deptno, d.dname, e.empno, e.ename
   Sort Key: d.deptno, e.empno
   ->  Hash Left Join
         Output: d.deptno, d.dname, e.empno, e.ename
         Hash Cond: (d.deptno = e.deptno)
         ->  Foreign Scan on _dblink_dept_1 d
               Output: d.deptno, d.dname, d.loc
               Remote Query: SELECT deptno, dname, NULL FROM dept
         ->  Hash
               Output: e.empno, e.ename, e.deptno
               ->  Seq Scan on public.emp e
                     Output: e.empno, e.ename, e.deptno
(13 rows)

The output of this join is the same as the previous left-outer-join example.

Creating a foreign table from a database link

After you create a database link, you can create a foreign table based on this database link. You can then use the foreign table to access the remote table, referencing it with the foreign table name instead of using the database link syntax. Using the database link requires appending @dblink to the table or view name referenced in the SQL command, where dblink is the name of the database link.

You can use this technique for either an oci-dblink connection for remote Oracle access or a postgres_fdw connection for remote Postgres access.

This example creates a foreign table to access a remote Oracle table.

The following creates a database link named oralink for connecting to the Oracle database:

CREATE PUBLIC DATABASE LINK oralink CONNECT TO edb IDENTIFIED BY 'password'
USING '//127.0.0.1:1521/xe';

The following query shows the database link:

SELECT lnkname, lnkuser, lnkconnstr FROM pg_catalog.edb_dblink;

 lnkname | lnkuser |     lnkconnstr
---------+---------+---------------------
 oralink | edb     | //127.0.0.1:1521/xe
(1 row)

When you create the database link, EDB Postgres Advanced Server creates a corresponding foreign server. The following query displays the foreign server:

SELECT srvname, srvowner, srvfdw, srvtype, srvoptions FROM
pg_foreign_server;

 srvname | srvowner | srvfdw | srvtype |          srvoptions
---------+----------+--------+---------+-------------------------------
 oralink |       10 |  14005 |         | {connstr=//127.0.0.1:1521/xe}
(1 row)

For more information about foreign servers, see the CREATE SERVER command in the PostgreSQL core documentation.

Create the foreign table:

CREATE FOREIGN TABLE emp_ora (
    empno           NUMERIC(4),
    ename           VARCHAR(10),
    job             VARCHAR(9),
    mgr             NUMERIC(4),
    hiredate        TIMESTAMP WITHOUT TIME ZONE,
    sal             NUMERIC(7,2),
    comm            NUMERIC(7,2),
    deptno          NUMERIC(2)
)
  SERVER oralink
  OPTIONS (table_name 'emp', schema_name 'edb'
);

Note the following in the CREATE FOREIGN TABLE command:

• The name specified in the SERVER clause at the end of the CREATE FOREIGN TABLE command is the name of the foreign server, which is oralink in this example. You can see this name in the srvname column from the query on pg_foreign_server.
• The table name and schema name are specified in the OPTIONS clause by the table and schema options.
• The column names specified in the CREATE FOREIGN TABLE command must match the column names in the remote table.
• Generally, CONSTRAINT clauses can't be accepted or enforced on the foreign table. They are assumed to have been defined on the remote table.

For more information about the CREATE FOREIGN TABLE command, see the PostgreSQL core documentation.

The following is a query on the foreign table:

SELECT * FROM emp_ora;

 empno | ename  |    job    | mgr  |      hiredate      |   sal   |  comm   | deptno
-------+--------+-----------+------+--------------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
(14 rows)

In contrast, the following is a query on the same remote table but using the database link instead of the foreign table:

SELECT * FROM emp@oralink;

 empno | ename  |    job    | mgr  |      hiredate      | sal  | comm | deptno
-------+--------+-----------+------+--------------------+------+------+--------
  7369 | SMITH  | CLERK     | 7902 | 17-DEC-80 00:00:00 |  800 |      |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 20-FEB-81 00:00:00 | 1600 |  300 |     30
  7521 | WARD   | SALESMAN  | 7698 | 22-FEB-81 00:00:00 | 1250 |  500 |     30
  7566 | JONES  | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975 |      |     20
  7654 | MARTIN | SALESMAN  | 7698 | 28-SEP-81 00:00:00 | 1250 | 1400 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850 |      |     30
  7782 | CLARK  | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450 |      |     10
  7788 | SCOTT  | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000 |      |     20
  7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000 |      |     10
  7844 | TURNER | SALESMAN  | 7698 | 08-SEP-81 00:00:00 | 1500 |    0 |     30
  7876 | ADAMS  | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100 |      |     20
  7900 | JAMES  | CLERK     | 7698 | 03-DEC-81 00:00:00 |  950 |      |     30
  7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000 |      |     20
  7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300 |      |     10
(14 rows)

See also

DROP DATABASE LINK