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をインストール


とりあえず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

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

root@zst:~# systemctl status postgresqlsystemctl 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