CREATE PUBLIC DATABASE LINK v16
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.
Oracle compatibility
- For EDB Postgres Advanced Server 16, the CREATE DATABASE LINK command has been tested and certified with all the minor versions for use with Oracle versions 10g Release 2, 11g Release 2, 12c Release 1, 18c Release 1, 19c, 21c, and 23.
Note
You can't use a database link to access a remote database in a standby database server. Standby database servers are for high availability, load balancing, and replication.
For information about high availability, load balancing, and replication for Postgres database servers, see the PostgreSQL core documentation.
You can set the
edb_dblink_oci.rescansGUC toSCROLLorSERIALIZABLEat the server level in thepostgresql.conffile. You can also set it at the session level using theSETcommand. However, the setting isn't applied to existing dblink connections due to dblink connection caching.When executing
SELECTon LOB data of more than 4000 characters, we recommend usingedb_dblink_oci.rescans=serializableto free up the temporary PGA memory and avoid exceedingPGA_AGGREGATE_LIMIT.
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.
Note
To create a non-public database link, you need the CREATE DATABASE LINK privilege. To create a public database link, you need the CREATE PUBLIC DATABASE LINK privilege.
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 libaioIf 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
Note
You must set either the LD_LIBRARY_PATH or the oracle_home configuration parameter and the ORACLE_HOME environment variable before executing the pg_ctl utility to start or restart EDB Postgres Advanced Server.
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.
Note
If tnsnames.ora is configured in failover mode, and a client:server failure occurs, the client connection is established with a secondary server (usually a backup server). Later, when the primary server resumes, the client retains its connection to a secondary server until a new session is established. The new client connections is automatically established with the primary server. If the primary and secondary servers are out of sync, then the clients that established a connection to the secondary server and the clients that later connected to the primary server might see a different database view.
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';
Note
You can specify a hostname in the connection string in place of an IP address.
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';
Note
You can specify a hostname in the connection string in place of an IP address.
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
Note
This procedure isn't compatible with Oracle databases.
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
SERVERclause at the end of theCREATE FOREIGN TABLEcommand is the name of the foreign server, which isoralinkin this example. You can see this name in thesrvnamecolumn from the query onpg_foreign_server. - The table name and schema name are specified in the
OPTIONSclause by thetableandschemaoptions. - The column names specified in the
CREATE FOREIGN TABLEcommand must match the column names in the remote table. - Generally,
CONSTRAINTclauses 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)
Note
For backward compatibility, you can still write USING libpq instead of USING postgres_fdw. However, the libpq connector is missing important optimizations that the postgres_fdw connector has. Therefore, use the postgres_fdw connector when possible. The libpq option is deprecated and might be removed in a future EDB Postgres Advanced Server release.