CTEでINSERTされた行のデータを別のINSERTに使う

前提

 CREATE TABLE category ( category_id serial primary key, category_name text );
 CREATE TABLE item ( item_id serial primary key, item_name text, category_id int );

CTE

 WITH category_cte AS (
     INSERT INTO category ( category_name ) VALUES ( 'cat_a' ) 
     RETURNING category_id
 )
 INSERT INTO item ( item_name, category_id ) 
 SELECT 'item_a', category_id FROM category_cte;
 
 [category]:
  category_id | category_name
 -------------+---------------
            1 | cat_a
 
 [item]:
  item_id | item_name | category_id
 ---------+-----------+-------------
        1 | item_a    |           1

トップ   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS