次のような要件を満たすSQLを「配列」と「CROSS JOIN」を使って作成する。

【前提】
A_TABLE (PK1, PK2, PK3, VAL1, CREATE_TS)
B_TABLE (PK1, PK2, VAL1, CREATE_TS)
※A_TABLEとB_TABLEのPK1とPK2、VAL1は同じ概念を指している。

【要件】
PK1 in (/*in values*/)を満たすデータがB_TABLEにだけあり、A_TABLEにも追加したい。
A_TABLEはB_TABLEが持っていない項目PK3を持っている。PK3には「'X', 'Y', 'Z'」の3種類を入れたい。
つまり、PK1 in (/*in values*/)を満たすデータがB_TABLEに5件あれば、A_TABLEには5件 * 3種類の15件を追加したい。

【解法】

  1. PK3に設定する「'X', 'Y', 'Z'」はどのテーブルにもないので、配列で定義してから、行集合に変換する。

    配列を行集合に変換するにはunnest(anyarray)関数を使用する。
    今回は、unnest(ARRAY['X', 'Y', 'Z'])。
    https://www.postgresql.jp/document/9.2/html/functions-array.html

    ※行から配列はarray(subquery)関数を使用する。

    #=select array(PK3) from A_TABLE limit 3
array
------
{X,Y,Z}

  2. A_TABLEには入れるのは、[B_TABLE] * [PK3のデータ]なので、B_TABLEとPK3の配列をCROSS JOINして、insert into TABLE select をしてあげる。

    insert into A_TABLE
select
PK1
, PK2
, DUPLICATE_KEY as PK3
, VAL1
,current_timestamp --#CREATE_TS
from B_TABLE
cross join (select unnest(ARRAY['X', 'Y', 'Z']) as DUPLICATE_KEY) x
where PK1 in (/*in values*/)
;