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

2020年04月22日 00時04分

今回は 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 の特色も違ってくるので勉強しがい、おいかけがいがある。

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