データベーステーブル比較

 SELECT CASE

WHEN f.bankcode IS NULL THEN v.bankcode

ELSE f.bankcode

END AS bankcode,

f.banknamef AS banknamef,

f.banknamek AS banknamek,

v.banknamef AS banknamef,

v.banknamek AS banknamek

FROM (SELECT *

FROM foreign_ccm102

EXCEPT

SELECT *

FROM view_ccm102) v

FULL JOIN (SELECT *

FROM view_ccm102

EXCEPT

SELECT *

FROM foreign_ccm102) f

ON f.bankcode = v.bankcode

ORDER BY bankcode;

postgres dblink postgres_fdw

dnf install postgresql15-contrib

\c workbase postgres

CREATE EXTENSION postgres_fdw;

CREATE SERVER arcsdbms FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '10.0.18.101', dbname 'arcsdbms', port '5432');

grant all on foreign server arcsdbms to sales;

\c workbase sales
CREATE USER MAPPING FOR sales SERVER arcsdbms

OPTIONS (user 'arcsdbcb', password 'xxxxxx00');

-----------------------------------------------------------

CREATE FOREIGN TABLE foreign_ccm102 (

bankcode varchar(4) ,

banknamef varchar(80),

banknamek varchar(80)

) SERVER arcsdbms

OPTIONS (schema_name 'arcsdbcb', table_name 'ccm102');

CREATE FOREIGN TABLE foreign_ccm103 (

bankcode varchar(4) ,

branchcode varchar(3),

branchnamef varchar(80),

branchnamek varchar(80) 

) SERVER arcsdbms

OPTIONS (schema_name 'arcsdbcb', table_name 'ccm103');

-----------------------------------------------------------

or

-----------------------------------------------------------

 IMPORT FOREIGN SCHEMA arcsdbcb

   LIMIT TO (ccm102, ccm103)

   FROM SERVER arcsdbms INTO sales;

-----------------------------------------------------------

SELECT count(*) FROM foreign_ccm102;

【DROP】

\c workbase sales

drop foreign table foreign_ccm103;

drop foreign table foreign_ccm102;

drop user mapping for  sales server arcsdbms;

\c workbase postgres

revoke all on foreign server arcsdbms from sales;

drop server arcsdbms;

drop extension postgres_fdw;

【ERROR】

psql:/dev/fd/63:2: ERROR:  機能拡張"postgres_fdw" は利用できません

DETAIL:  機能拡張の制御ファイル"/usr/pgsql-15/share/extension/postgres_fdw.control"をオープンできませんでした: そのようなファイルやディレクトリはありません

HINT:  PostgreSQLが稼働しているシステムで、事前に機能拡張がインストールされている必要があります。

→　 dnf install postgresql15-contrib