PostgreSQLでdblinkを使用して、異なるデータベースのテーブルを結合する

JavaScript

PostgreSQLで、異なるデータベースのテーブルを結合したい場合、dblinkを使用すると便利です。
dblinkを使用するための準備と、使用方法について説明します。


環境

  • OS:Ubuntu Server 22.04.2 LTS
  • PostgreSQL15.5

dblinkの準備

dblinkを使用するためには、PostgreSQLのエクステンションをインストールしておく必要があります。

postgres=# create extension dblink;
postgres=# \dx
                                    インストール済みの拡張一覧
  名前   | バージョン |  スキーマ  |                             説明
---------+------------+------------+--------------------------------------------------------------
 dblink  | 1.2        | public     | connect to other PostgreSQL databases from within a database
 plpgsql | 1.0        | pg_catalog | PL/pgSQL procedural language
(2 行)

dblinkを使用

dblinkを使用するために参照するためのテーブルは、あらかじめviewとして準備しておくと便利です。

// 「DBLINK-SAMPLE」という名称でビューを作成しておく
create or replace view link_view_sample as
select
    col1
    , col2 
    , col3
from
    dblink(
        'DBLINK-SAMPLE'
        ,'select
            col1
            , col2
            , col3
        from
            other_server_table otTble
            left outer join my_server_table myTble on otTble.col1 = myTble.kojin_col1
        ) AS t(
            col1 int
            , col2 int
            , col3 int
    );

作成したビューを使用するためには、リンク先サーバと接続→SQLを実行→リンク先サーバとの接続解除、の流れになります。


// リンク先サーバと接続
select
    dblink_connect( 
        'DBLINK-SAMPLE'
        , 'host=X.X.X.X dbname=database user=saku password=saku00'
    );

// ビューを参照
select
    col1
    , col2
    , col3 
from
    link_view_sample;

// リンク先サーバとの接続解除
select dblink_disconnect('DBLINK-SAMPLE');

 


Ubuntu22にPostgreSQL15をインストールする

Linux

Ubuntu22にPostgre15をインストールする方法について紹介します。
途中、少し手こずったので、対処方法についても紹介します。


環境


  • Ubuntu Server 22.03.3 LTS
  • PostgreSQL15.4
  • rootユーザ

PostgreSQLのバージョン確認


まず、Ubuntu22でのPostgreSQL標準バージョンを確認します。

apt info postgresql

以下のバージョンが標準バージョンとなります。

Package: postgresql
Version: 14+238

Ubuntu構築時点では認識できる標準バージョンが古い場合があるので、レポジトリを追加して、再度、PostgreSQL標準バージョンを確認します。

apt install curl ca-certificates gnupg 
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor | sudo tee /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg >/dev/null
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
cat /etc/apt/sources.list.d/pgdg.list 
↓レポジトリが追加されていることを確認
deb http://apt.postgresql.org/pub/repos/apt jammy-pgdg main
↑レポジトリが追加されていることを確認

apt update
apt info postgresql
↓標準バージョンが変化していることを確認
Package: postgresql
Version: 15+253.pgdg22.04+1
↑標準バージョンが変化していることを確認

 


PostgreSQLをインストール


とりあえず、aptコマンドを使用してPostgreSQLをインストールしてみます。


apt install -y postgresql

以下のメッセージが表示されてエラーとなります。

E: Failed to fetch http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-15/postgresql-15_15.4-1.pgdg22.04%2b1_amd64.deb  Connection failed [IP: 87.238.57.227 80]		
E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?		

“apt-get update”をやってみろと言われているので、素直にやってみます。
依存関係の更新ですね。

apt-get update
apt-get install
apt install -y postgresql

しかし、同じエラーとなってしまいました。

E: Failed to fetch http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-15/postgresql-15_15.4-1.pgdg22.04%2b1_amd64.deb  Connection failed [IP: 147.75.85.69 80]	
E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?	

パッケージを手動ダウンロードして再インストール


パッケージのダウンロードでエラーとなっているようなので、手動でダウンロードしてみます。
手動でフォルダを作成し、そのフォルダにダウンロードします。

mkdir -p /tmp/
cd /tmp/
wget http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-15/postgresql-15_15.4-1.pgdg22.04%2b1_amd64.deb
ls -ltr
↓パッケージがダウンロードされていることを確認
postgresql-15_15.4-1.pgdg22.04+1_amd64.deb
↑パッケージがダウンロードされていることを確認

ダウンロードできたかことが確認できたら、ダウンロードしたパッケージのみをインストールします。


dpkg -i ./postgresql-15_15.4-1.pgdg22.04+1_amd64.deb

再度、インストールを実施してみます。

apt install -y postgresql

以下のエラーになってしまいます。

E: Unmet dependencies. Try 'apt --fix-broken install' with no packages (or specify a solution).

“–fix-broken”オプションを付けて依存関係を更新しろといわれているのでやってみます。

apt --fix-broken install

再度インストールをやってみます。

apt install -y postgresql

正常にインストールが終了したら、「systemctl」で起動していることを確認して終了です。

root@zst:~# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor pres
     Active: active (exited) since Sun 2023-09-03 11:07:21 UTC; 7s ago
    Process: 68255 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
   Main PID: 68255 (code=exited, status=0/SUCCESS)
        CPU: 3ms
Sep 03 11:07:21 zst systemd[1]: Starting PostgreSQL RDBMS...
Sep 03 11:07:21 zst systemd[1]: Finished PostgreSQL RDBMS.


バッチからSQLを実行し、かつSQLにパラメータも渡す方法

データベース

Windowsのバッチファイルを作って、そのバッチファイルからSQLを呼び出すことは多々あるかと思います。
今回は、バッチファイルからSQLを呼び出す方法と、SQLにパラメータを渡す方法について紹介していきたいと思います。


環境


  • DB:PostgreSQL9.6

バッチファイル


まずはバッチファイルです。
WindowsのBATファイルとして準備します。


SET HOST=localhost
SET USERID=postgres
SET PORT=17102
SET DBNAME=SAMPLE_DB
SET PARAM=100

psql.exe -h %HOST% -U %USERID% -p %PORT% -d %DBNAME% -f sample.sql PARAM=%PARAM%

SQLファイルに渡すパラメータは、DBのホスト名やユーザIDと同じように「PARAM」という名称で、パラメータを準備しています。


SQLファイル


次にSQLファイルです。
テーブル名はすべてサンプルになります。


BATファイルに定義した「PARAM」を使用するSQLを実行することができます。


SELECT
    id,
FROM
    SAMPLE_TABLE
WHERE
    id = :PARAM

SQLに記載するパラメータは、コロン(:)をキーワード前方に付加することで、SQL内で認識することができます。



SQLの結合3パターン。クロスジョインはカラム列からレコード行への変換で使おう。

データベース

SQLのテーブル結合は、以下の3種類の方法があります。


  • 内部結合
  • 外部結合
  • クロス結合

取得したいデータの内容によって、結合の方法を使い分ける必要があります。


今回は、3種類の結合方法についてPostgreSQLを使って解説していきます。


環境情報


  • DB:PostgreSQL9.6

テーブル定義と格納データ


解説で使用するテーブル定義と格納データは以下になります。

『役職テーブル』と『社員テーブル』の、2つのテーブルを想定します。


create table yakusyoku (
  yakusyoku_id serial not null
  , yakusyoku_name varchar(10) not null
  , constraint yakusyoku_pkey primary key (yakusyoku_id)
) ;

create table syain (
  syain_id serial not null
  ,yakusyoku_id integer
  , syain_name varchar(10) not null
  , constraint syain_pkey primary key (syain_id)
) ;
"yakusyoku_id"	"yakusyoku_name"
-----------------------------------
1				"部長"
2				"課長"
3				"係長"
4				"主任"
"syain_id"	"yakusyoku_id"	"syain_name"
----------------------------------------------
1			1				"山田部長"
2			2				"鈴木課長"
3			3				"佐藤係長"
4			3				"佐藤係長"

内部結合/依存関係ありのデータを取得する


内部結合は、依存関係(レコード間のリレーション)があるデータのみを取得します。
今回の例では、役職が付いている社員のみが取得するSQLを実現することができます。


SELECT
    A.yakusyoku_id	
    A.yakusyoku_name	
    B.syain_id	
    B.syain_name
FROM
    yakusyoku as A INNER JOIN syain as B 
ON 
    A.yakusyoku_id = B.yakusyoku_id;
"yakusyoku_id"	"yakusyoku_name"	"syain_id"	"syain_name"
-----------------------------------------------------------
1				"部長"				1			"山田部長"
2				"課長"				2			"鈴木課長"
3				"係長"				3			"佐藤係長"
3				"係長"				4			"佐藤係長"

外部結合/依存関係なしのデータも取得する


内部結合は、依存関係がないデータのみも取得します。
今回の例では、主任の社員はいないのですが、役職テーブルのメインテーブルとして外部結合をおこなっているので、主任レコードも取得できます。


SELECT
    A.yakusyoku_id	
    A.yakusyoku_name	
    B.syain_id	
    B.syain_name
FROM
    yakusyoku as A LEFT OUTER JOIN syain as B 
ON 
    A.yakusyoku_id = B.yakusyoku_id;
"yakusyoku_id"	"yakusyoku_name"	"syain_id"	"syain_name"
-----------------------------------------------------------------
1				"部長"				1			"山田部長"
2				"課長"				2			"鈴木課長"
3				"係長"				3			"佐藤係長"
3				"係長"				4			"佐藤係長"
4				"主任"				""			""

クロス結合/組み合わせを取得する


クロス結合の基本文法


クロス結合は、レコード同士の組み合わせを取得します。
今回の例では、全ての役職と全ての社員の組み合わせを取得できます。


結合条件も指定しないので、役職テーブルの主任レコードに対する組み合わせについても取得できます。


SELECT
    A.yakusyoku_id	
    A.yakusyoku_name	
    B.syain_id	
    B.syain_name
FROM
    yakusyoku as A CROSS JOIN syain as B;
"yakusyoku_id"	"yakusyoku_name"	"syain_id"	"syain_name"
--------------------------------------------------------------
1				"部長"				1			"山田部長"
1				"部長"				2			"鈴木課長"
1				"部長"				3			"佐藤係長"
1				"部長"				4			"佐藤係長"
2				"課長"				1			"山田部長"
2				"課長"				2			"鈴木課長"
2				"課長"				3			"佐藤係長"
2				"課長"				4			"佐藤係長"
3				"係長"				1			"山田部長"
3				"係長"				2			"鈴木課長"
3				"係長"				3			"佐藤係長"
3				"係長"				4			"佐藤係長"
4				"主任"				1			"山田部長"
4				"主任"				2			"鈴木課長"
4				"主任"				3			"佐藤係長"
4				"主任"				4			"佐藤係長"

クロス結合の使いどころ。横→縦変換


クロスジョインはあまり用途がないように思いますが、よく使う用途としては横→縦変換になります。


横→縦変換とは、カラム毎に格納されている値をレコード事に格納することです。
以下のようなイメージですね。



以下に具体的を使って説明しています。

テーブルの横→縦変換をおこなってみます。


create table col_test (
  col_test_id serial not null
  , name1 varchar(10) not null
  , name2 varchar(10) not null
  , name3 varchar(10) not null
  , name4 varchar(10) not null
  , name5 varchar(10) not null
  , constraint col_test_id_pkey primary key (col_test_id)
) ;
	"name1",	"name2",	"name3",	"name4",	"name5"
--------------------------------------------------------------
1,	"名称1_A",	"名称2_B",	"名称3_C",	"★名称4_D","名称5_E"
2,	"★名称1_B","名称2_B",	"名称3_B",	"名称4_B",	"名称5_B"
3,	"名称1_C",	"★名称2_C","★名称3_C","名称4_C",	"名称5_C"

このテーブルから、名称の先頭に「★」が付いている値を行データとして取得するとします。 この場合、縦→横変換をおこなうと便利です。


縦→横変換をおこなうために、テンポラリテーブル(TEMPORARY TABLE)
を使っています。
テンポラリテーブルで、1~5の値が入っているのみのテーブルを作成しています。


このテンポラリテーブルと対象テーブル(ここではpivot)をCROSS JOINを使って組み合わせを作成し、その組み合わせで名称の先頭に「★」ついているレコードのみを取得しています。


-- テンポラリテーブルの作成
CREATE TEMPORARY TABLE pivot (
 val integer PRIMARY KEY
);

-- テンポラリテーブルに1~5の値が入った状態にする
INSERT INTO pivot with recursive rec(val) as (
 values(1)
 union all
 select 
    Val+1
 from 
    rec
 where 
    Val+1 <= 5
) select Val from rec;

-- 名称の先頭に「★」がついたレコードのみを抽出
SELECT * FROM (
    SELECT
        case P.val
            when 1 then A.name1
            when 2 then A.name2
            when 3 then A.name3
            when 4 then A.name4
            when 5 then A.name5
        end as cross
    FROM
        col_test as A CROSS JOIN pivot P) AS X
WHERE
    X.cross like '★%';
"cross"
-------------------
★名称1_B
★名称2_C
★名称3_C
★名称4_D