WITH句を使ったINSERTの書き方

WITH句を使った検索クエリは、

WITH  xxxx AS (
    ～
)
SELECT * FROM xxxx

のように、WITH句の後で、SELECT ～ FROM with句で定義した名称
と書くものだが、
INSERT INTO ～ SELECT で使用する場合、
INSERT INTO の前に書くのか？、後ろに書くのか？

どうもデータベースによって違うようである。
PostgreSQL は、
マニュアル
https://www.postgresql.jp/docs/17/queries-with.html#QUERIES-WITH-MODIFYING
に書いてあるように、 INSERT INTO の前に書く
サンプル

WITH temp AS (
   SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
   FROM TEST_USERS
   WHERE AGE > 29
) 
INSERT INTO CUSTOMERS (
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
)
SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
FROM GUESTS a 
INNER JOIN temp b ON a.LAST_NAME = b.LAST_NAME

しかし、Oracle ではこれはエラーになってしまう。
WITH句はSELECTの前、
INSERT INTO と SELECT の間に書かないと Oracle では動かない。

INSERT INTO CUSTOMERS (
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
)
WITH temp AS (
   SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
   FROM TEST_USERS
   WHERE AGE > 29
) 
SELECT 
        FIRST_NAME,
        LAST_NAME,
        PHONE_NUMBER
FROM GUESTS a 
INNER JOIN temp b ON a.LAST_NAME = b.LAST_NAME

トリッキーな、SELECT * FROM dual

Oracleでは、

SELECT * FROM dual

は、列名＝'DUMMY' 値＝'X'の1行を返す。

PostgreSQL では、、、
dual は存在しないのでFROM句を省略するのだが、

SELECT * 

とすると、
　　SQLエラー [42601]: ERROR: テーブル指定のないSELECT *は無効です
になる
Oracleと同じことをするには、WITH句でdualを用意する

WITH dual AS(
   SELECT 'X' AS DUMMY
)
SELECT * FROM dual

これでOracleと同じことができる。

MySQLでは、

SELECT 1 FROM dual

は、列名＝'1' 値＝1 が返ってくるしFROMを省略して

SELECT 1

でも同じである。

ただ上に書いた * （アスタリスク）はどうなるかというと、、

SELECT *

や、

SELECT * FROM dual

は、
　　SQLエラー [1096] [HY000]: No tables used
となる。
でも、

 SELECT COUNT(*) AS cnt  FROM DUAL

や、

SELECT COUNT(*) AS cnt

は、きちんと COUNT = 1 が返ってくる

DBeaver のドライバの設定

DBeaver をインストールした環境でそうそう巡り合わないのだが、
ドライバ入手先へのネットワーク接続が制限された環境でDBeaver が使用できなくて
困ってしまうことがあります。
そういう場合は、なんとか JDBCドライバファイルを入手してPC上に置いて
それを使用するように設定します。
各DB接続で必要なドライバは、接続設定を開いて、

一般タブの右下にある「ドライバの設定を編集」ボタンで開いた画面の
[ライブラリ] タブを開き認識します。

これがデフォルトで、ドライバをネットワークから入手する設定なので.
ドライバ設定のJARファイル名をメモをしておきます。

メモしたら、これら全てを削除して、
Mavenのリポジトリhttps://mvnrepository.com/　などから
JAR ファイルをダウンロードして適当な場所に置き、
「ファイルを追加」でダウンロードしたJARを指定します。

これで、ドライバ入手先へのネットワーク接続が制限された環境でも使用できます

REGEXP_REPLACEのフラグ

REGEXP_REPLACEで使用できるフラグは、
Oracle とPostgreSQLで以下の差がある。

Oracle も PostgreSQL も同じフラグ

c	大文字小文字を区別する一致
i	大文字小文字を区別しない一致

Oracle のフラグ

n	ドット演算子(.)を改行文字と一致させることができる
x	検索パターン内の空白文字が無視される

PostgreSQL のフラグ

n

改行を区別する一致

SQL JOIN で展開されるカウントをクエリ内で使う方法

結合しない対象テーブル１個だけなら、GROUP BY 句と COUNT関数を使うが、
JOIN で結合したときの展開されるレコード数を求めるあるいは使用した
クエリはどうずれば良いか？
目的が、
　　結合元テーブルの任意カラムの数値 ／ 結合したときの展開されるレコード数
を１度のクエリで求めることである。

方法は２通りある。
（１）GROUP BYでカウントしたクエリと結合する方法
（２）ウィンドウ関数 OVER句を使って COUNTを求める方法

データベースが PostgreSQL で、以下のテーブルがあるとする。

CREATE TABLE items (
    id int4 NOT NULL,
    task_name varchar(60) NULL,
    point int4 DEFAULT 0 NOT NULL,
    CONSTRAINT items_pkey PRIMARY KEY (id)
)

CREATE TABLE itemcodes (
    task_name varchar(60) NOT NULL,
    task_code varchar(60) NULL
);

この items の task_name が、itemcode の task_name で結合しているとする。
求めたいのは、point が、 結合したときの展開されるレコード数で除算した値である。

（１）GROUP BYでカウントしたクエリと結合する方法

SELECT 
     a.task_name 
    ,b.task_code
    ,a.point
    ,c.t_cnt
    ,ROUND(a.point * 1.0 / c.t_cnt, 6) AS a_value
FROM items a
LEFT JOIN itemcodes b ON a.task_name = b.task_name
INNER JOIN (
    SELECT 
        task_name
        ,COUNT(task_name) AS t_cnt
    FROM itemcodes
    GROUP BY task_name
) c
ON b.task_name = c.task_name
ORDER BY a.task_name ASC

結合が増えて長くなるので良いとは思えない

（２）ウィンドウ関数 OVER句を使って COUNTを求める方法

SELECT 
     a.task_name 
    ,b.task_code
    ,a.point
    ,COUNT(b.task_name) OVER (PARTITION BY b.task_name) AS t_cnt
    ,ROUND(a.point * 1.0 / COUNT(b.task_name) OVER (PARTITION BY b.task_name), 6) AS a_value
FROM items a
LEFT JOIN itemcodes b ON a.task_name = b.task_name
ORDER BY a.task_name ASC

OVER句で、結合カラムで区切ることを示す列をPARTITION BY で指定する。

PlantUML Activity repeat backward にサブダイアグラムを使う

先日、Swim Lane を使用した PlantUML Activity で悩んだ。
oboe2uran.hatenablog.com

この時のもう１つの解としてサブダイアグラムで前のレーンで実行することを
表現したらどうであろう。

@startuml

|User|
start
:新規作成;

|システム|
repeat 

backward:{{
  "User"#fce2a0->[ ] "修正"
}};

|システム|
repeat while (検証?) is (差戻し)
:登録;

end

@enduml

サブダイアグラムは、{{ }} で囲って記述する
https://plantuml.com/ja/sub-diagram

VScode の markdown で表の罫線を出したい

VScode で markdown 表(Table)のプレビューで罫線が出ないのが標準のようである。
使用している拡張機能は、Markdown All in One

CSS で table タグのスタイルを指定させて解決する。

VScode ワークスペースを使用していることが前提で、
ワークスペースの設定を開く
　コマンドパレットを開く（ctrl＋Shift＋P）
　「Preferences: Open User Settings (JSON)」 を選択。
　ワークスペース直下の　.vscode フォルダ内に settings.json が存在しなければ、
　作成する。UTF-8 で良い。
JSON で、markdown.styles　を書く。
CSS設定ファイル style.css の指定は、ワークスペースからのPATHで指定しなければならない。
注意が必要なのは、WindowsのPATH区切り文字 '\' １個ではなく２個で書かないとならない。
.vscode フォルダ内に配置した場合は以下のとおり。

{
    "markdown.styles": [".vscode\\style.css"],
}

table のスタイルを style.css に書く。boder とヘッダのテキスト Bold 指定だけで済ませる。

table {
    border-collapse: collapse;
    border: 1px solid #000000 !important;
}
table th {
    font-weight: bold;
    border: 1px solid #000000 !important;
}
table td {
    border: 1px solid #000000 !important;
}

結果の確認、PDFで
VScode のマークダウンをPDF にエクスポートするのに以下を使う。
PDF以外にHTML 、JPEG、PNG にエクスポートできるので、よくこれが使われているようだ。

エクスポートは、マークダウンを開いている状態で、コマンドパレットから
　markdown pdf と入力して選択する。

PCのスペックによると思うが、以外と時間かかる。