データ処理の過程で、データが他のデータベース（スキーマ）にある場合で同じインスタンスにある場合に、テーブルのデータを毎回EXPORTしてインポートしたりするのは手間になります。そんな場合に、MySQLとPostgreSQLではどの様にデータベースを跨ぐ処理を実行するか参考の為にメモしておきます。
MySQL
MySQLの場合は、データベースを跨ぐDMLの場合はシンプルに以下の様にデータベース名.テーブル名を指定すればOKなので非常にシンプルに扱う事が出来る。
root@localhost [POC]> CREATE TABLE `t1` (
-> `id` int NOT NULL AUTO_INCREMENT,
-> `note` varchar(255) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.34 sec)
root@localhost [POC]> insert into t1(note) values('This is cross schema check');
Query OK, 1 row affected (0.01 sec)
root@localhost [POC]> insert into t1(note) values('This is cross schema check');
Query OK, 1 row affected (0.00 sec)
root@localhost [POC]> insert into t1(note) values('This is cross schema check');
Query OK, 1 row affected (0.01 sec)
root@localhost [POC]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
root@localhost [mysql]> select * from POC.t1;
+----+----------------------------+
| id | note |
+----+----------------------------+
| 1 | This is cross schema check |
| 2 | This is cross schema check |
| 3 | This is cross schema check |
+----+----------------------------+
3 rows in set (0.00 sec)
root@localhost [mysql]> 
PostgreSQL
PostgreSQLの場合は、他のデータベース（スキーマ）から他のデータベースのテーブルをそのまま参照する事は出来ないので、以下の様にpostgres_fdw等を利用して設定してあげる必要がある。ここは、MySQLやMS SQLを使い慣れているユーザーには不便に感じる事もあるかと思います。
postgres_fdwモジュールは、外部のPostgreSQLサーバに格納されたデータをアクセスするために使用する、postgres_fdw外部データラッパを提供します。
~/git/rdbms-docker/postgresql [ 9:40:19]> psql -h 127.0.0.1 -p 5432 -U postgres
Password for user postgres:
psql (14.1 (Ubuntu 14.1-1.pgdg18.04+1))
Type "help" for help.
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
POC | postgres | UTF8 | C | C | | 8681 kB | pg_default |
postgres | postgres | UTF8 | C | C | | 8553 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | C | C | =c/postgres +| 8401 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C | C | =c/postgres +| 8401 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
postgres=# create database OTHER owner 'postgres' encoding 'UTF8' lc_collate 'C' lc_ctype 'C' template 'template0';
CREATE DATABASE
postgres=# \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
POC | postgres | UTF8 | C | C | | 8681 kB | pg_default |
other | postgres | UTF8 | C | C | | 8401 kB | pg_default |
postgres | postgres | UTF8 | C | C | | 8553 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | C | C | =c/postgres +| 8401 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C | C | =c/postgres +| 8401 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 rows)
postgres=# \c POC
You are now connected to database "POC" as user "postgres".
POC=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
public | test | table | postgres
(2 rows)
POC=# \c other
You are now connected to database "other" as user "postgres".
other=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
other=# CREATE SERVER link_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost',port '5432',dbname 'POC');
CREATE SERVER
other=# create user mapping for postgres server link_server options (user 'postgres', password 'password');
CREATE USER MAPPING
other=# import foreign schema public limit to (t1) from server link_server into public;
IMPORT FOREIGN SCHEMA
other=# \d
List of relations
Schema | Name | Type | Owner
--------+------+---------------+----------
public | t1 | foreign table | postgres
(1 row)
other=# select * from t1;
id | note
----+-----------------------------
1 | This is Sequence Lock Check
2 | This is Sequence Lock Check
3 | This is Sequence Lock Check
4 | This is Sequence Lock Check
5 | This is Sequence Lock Check
6 | This is Sequence Lock Check
7 | This is Sequence Lock Check
8 | This is Sequence Lock Check
9 | This is Sequence Lock Check
10 | This is Sequence Lock Check
(10 rows)
other=#