Use CREATE [PUBLIC] DATABASE LINK to create database links - PolarDB

Creates a database link — a named object stored in the local database that lets you reference tables and views in a remote database directly in DELETE, INSERT, SELECT, and UPDATE statements.

Prerequisites

Before you begin, ensure that you have the oracle_fdw or postgres_fdw extension installed. If not, run CREATE EXTENSION oracle_fdw or CREATE EXTENSION postgres_fdw.

Synopsis

Connect to a PolarDB for PostgreSQL (Compatible with Oracle) database:

CREATE [ PUBLIC ] DATABASE LINK <name>
   CONNECT TO <username> IDENTIFIED BY "<password>"
  USING ( channel_name '<channel_name>', dbname '<db_name>' );

Connect to an Oracle database:

CREATE [ PUBLIC ] DATABASE LINK <name>
   CONNECT TO <username> IDENTIFIED BY "<password>"
  USING '//<channel_name>/<dbname>';

Parameters

Parameter	Description
`PUBLIC`	Makes the database link available to all users. Without this parameter, the link is private and accessible only to its owner.
`name`	The name of the database link.
`username`	The username used to connect to the remote database.
`password`	The password used to connect to the remote database.

Usage notes

Use the table@link_name syntax to reference a table or view on the remote database in DELETE, INSERT, SELECT, and UPDATE statements.
Without PUBLIC, the database link is private — accessible only to the user who created it.
To list existing database links, query the DBA_DB_LINKS, ALL_DB_LINKS, or USER_DB_LINKS views.

Examples

Connect to an Oracle database

Create a private database link to an Oracle database using oracle_fdw:

CREATE DATABASE LINK ora_dblink
  CONNECT TO admin IDENTIFIED BY 'mypassword'
  USING '//orachannel/acctg';

Connect to a PolarDB for PostgreSQL database

Create a private database link to a PolarDB for PostgreSQL (Compatible with Oracle) database using postgres_fdw:

CREATE DATABASE LINK pg_dblink
  CONNECT TO admin IDENTIFIED BY "mypassword"
  USING (channel_name 'localhost', dbname 'marketing');

Use the database link

Query a single remote table:

SELECT * FROM emp@ora_link;

Sample result:

 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
(4 rows)

Join tables across two database links:

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

Sample result:

 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)