PostgreSQL 親テーブルをインサートしつつ外部キー制約のある関連テーブルにもインサートする

今回はPostgres10.7互換のAWS Auroraで親テーブルをインサートしつつ関連する子テーブルにも値を入れたい時に1つのクエリでできないかなーと試行錯誤した感じです。

親テーブル(sheets) シート名

項目名
id serial
label varchar
created_at timestamp

子テーブル(input_contents) 入力内容

項目名
sheet_id int4
label varchar
content varchar

クエリ

–親テーブルインサート用の定義
with insertinfo as (
INSERT INTO sheets
(label, created_at)
VALUES(‘test’, ‘2020-12-31’)
–登録後のidを返り値として定義する
RETURNING cast(id as int4) AS sheet_id)
–子テーブルインサート用データ定義(ここをリソルバ―等で記載していく)
, checkdata (“label”, content) as
( VALUES
(‘label’, ‘sample’)
)
–INSERT … SELECTを使用する。 CROSS JOINで親テーブル結果と子テーブルのデータ定義を結合させてインサートする
INSERT INTO input_contents
(sheet_id, “label”, “content”)
select insertinfo.sheet_id , checkdata.”label”, checkdata.”content” from insertinfo
CROSS JOIN checkdata

クエリ説明

親要素インサート部分1

–親テーブルインサート用の定義
with insertinfo as (
INSERT INTO sheets
(label, created_at)
VALUES(‘test’, ‘2020-12-31’)
–登録後のidを返り値として定義する
RETURNING cast(id as int4) AS sheet_id)

with句でインサートするのはできるDBとできないDBがあるので要相談。

キャストしないと怒られたのでキャストはしている。

インサートして、RETURNINGでidを受け取るまでが役割。

データ定義部分

, checkdata (“label”, content) as
( VALUES
(‘label’, ‘sample’)
)

ここの記載は子テーブルにインサートするデータを定義する必要がある。

バルクインサートも検討したかったんだけれども、今回はリソルバ―に頼るつもり。一旦ローカルで動かすために自分でデータ定義をしている。

子テーブルインサート部分

–INSERT … SELECTを使用する。 CROSS JOINで親テーブル結果と子テーブルのデータ定義を結合させてインサートする
INSERT INTO input_contents
(sheet_id, “label”, “content”)
select insertinfo.sheet_id , checkdata.”label”, checkdata.”content”, from insertinfo
CROSS JOIN checkdata

INSERT … SELECTを使用している。

SELECT句でインサート用のデータを定義してインサートしている。

今回はsheet_idは一つの値でよかったのでCROSS JOINで各子テーブルデータと結合している。

 

感想

SQL一つとっても奥が深いし、DBの特色も違ってくるので勉強しがい、おいかけがいがある。

どうしても普通の開発だと納期の兼ね合いで追及できない部分も個人開発なら納得のできるものになるまで待てるのがいい。

シェアする

  • このエントリーをはてなブックマークに追加

フォローする