Yanor.net/
Wiki
Blog
GitHub
Sandbox
開始行:
* CTEでINSERTされた行のデータを別のINSERTに使う [#i5b52873]
** 前提 [#uddfed02]
CREATE TABLE category ( category_id serial primary key, ...
CREATE TABLE item ( item_id serial primary key, item_nam...
- categoryテーブルにレコードを挿入後、そのレコードのプラ...
** CTEを使う場合 [#la2034b7]
WITH category_cte AS (
INSERT INTO category ( category_name ) VALUES ( 'cat...
RETURNING category_id
)
INSERT INTO item ( item_name, category_id )
SELECT 'item_a', category_id FROM category_cte;
- categoryテーブルにINSERTするCommon Table Expression(CTE...
- itemテーブルのINSERTにはCTEが返すデータを使う
** シーケンスを参照する場合 [#caecf824]
*** lastval() [#e1fbc2a1]
INSERT INTO category ( category_name ) VALUES ( 'cat_b' );
INSERT INTO item ( item_name, category_id ) VALUES ( 'it...
*** currval() [#f1bd0983]
INSERT INTO category ( category_name ) VALUES ( 'cat_c' );
INSERT INTO item ( item_name, category_id ) VALUES ( 'it...
currval(pg_get_serial_sequence('category', 'category...
** 参考 [#r22cfe59]
- https://www.postgresql.jp/document/9.6/html/dml-returni...
- https://rob.conery.io/2015/02/08/inserting-using-new-re...
終了行:
* CTEでINSERTされた行のデータを別のINSERTに使う [#i5b52873]
** 前提 [#uddfed02]
CREATE TABLE category ( category_id serial primary key, ...
CREATE TABLE item ( item_id serial primary key, item_nam...
- categoryテーブルにレコードを挿入後、そのレコードのプラ...
** CTEを使う場合 [#la2034b7]
WITH category_cte AS (
INSERT INTO category ( category_name ) VALUES ( 'cat...
RETURNING category_id
)
INSERT INTO item ( item_name, category_id )
SELECT 'item_a', category_id FROM category_cte;
- categoryテーブルにINSERTするCommon Table Expression(CTE...
- itemテーブルのINSERTにはCTEが返すデータを使う
** シーケンスを参照する場合 [#caecf824]
*** lastval() [#e1fbc2a1]
INSERT INTO category ( category_name ) VALUES ( 'cat_b' );
INSERT INTO item ( item_name, category_id ) VALUES ( 'it...
*** currval() [#f1bd0983]
INSERT INTO category ( category_name ) VALUES ( 'cat_c' );
INSERT INTO item ( item_name, category_id ) VALUES ( 'it...
currval(pg_get_serial_sequence('category', 'category...
** 参考 [#r22cfe59]
- https://www.postgresql.jp/document/9.6/html/dml-returni...
- https://rob.conery.io/2015/02/08/inserting-using-new-re...
ページ名: