PK（主キー）で紐づけられたテーブルへのカラム・レコードの追加

Question

主キーを基にテーブルＡにテーブルＢの列・レコードを追加したいと考えています。

下図のイメージでテーブルを生成したく思い、ご教示いただければ、幸いです。
環境はpostgrespl（イントラマート）になります。

テーブルＡ　　　　　　　　　　　　　　

主キー	カラムA	カラムB
001	グループ１	野菜

テーブルＢ

主キー	行番号	カラムC
001	１	ごぼう
001	２	ほうれん草

実現したい形（テーブルA）

|主キー|カラムA|カラムB|カラムD|カラムE|
|:--|:--:|:--:|--:|
|001|グループ１|野菜|ごぼう|ほうれん草|

Comments

[hoshi-takanori]

これは難しいですね。テーブル B には同じ主キーのものが 2 件ずつ入ってるのでしょうか？ また、その 2 件をどういう基準でカラム D と E に分けるのでしょうか？

[guhi]

ご連絡ありがとうございます。

＞テーブル B には同じ主キーのものが 2 件ずつ入ってるのでしょうか
同じ主キーのものが２件入ります。

＞また、その 2 件をどういう基準でカラム D と E に分けるのでしょうか？
実際にはテーブルBに行番号があり、行番号１がカラムＤ、行番号２がカラムＥとなります。

Answer 1

以下の様なSELECTを元に処理すればば良いかと思います。
カラムの並びを指定したい場合はarray_agg()(※9.21.集約関数)で指定します。
※行番号が1,2共に存在していることが前提です。（1の欠番が無い）

with 
  tblA(主キー, カラムA, カラムB) as (
    values ('001','グループ1','野菜')
  ), tblB(主キー, 行番号, カラムC) as (
    values ('001',1,'ごぼう'), ('001',2,'ほうれん草')
  )
select ta.*, tb.カラムagg[1] as カラムD, tb.カラムagg[2] as カラムE
from  tblA ta
      inner join (
        select 主キー, array_agg(カラムC order by 行番号) as カラムagg
        from  tblB
        group by 主キー
      ) tb
      on    ta.主キー=tb.主キー 

※with部分はテーブルA,Bの値を指定している部分ですので、実際には省いて下さい。

質問ではinsertになっていますが、テーブルAに対して行うなら、updateですね。

update tblA tgt set
  カラムD=src.カラムagg[1]
, カラムE=src.カラムagg[2]
from (
        select 主キー, array_agg(カラムC order by 行番号) as カラムagg
        from  tblB
        group by 主キー
     ) src
where tgt.主キー=src.主キー

追加も更新も必要なら、insert into on conflict でupsertするのが効率良いかと思います。

Comments

[guhi]

アドバイスいただいた内容で実現できました。
ご回答ありがとうございました。