為多個級聯 1:M 子表插入重複行

想像多個具有 1:M 關係的父子表。我想“級聯”-根據根父表行選擇插入重複的行。每個表都有IDENTITY主鍵，每個子表都有與其父 ID 的 FK（上一級）。

目標

給定根父表 ID，為其及其所有子表插入重複行。

我嘗試了“級聯”-插入 CTE，但遇到了RETURNING僅限於返回插入數據的問題，而我需要額外的資訊來連接下一個INSERT.

我可以通過添加一個額外的列 ( copied_from_id) 來實現這一點。

我的問題

有沒有辦法在沒有額外列的情況下完成同樣的任務？

我確實看到了@Erwin Brandstetter 的這個答案，但他的例子只有 1 個父母和孩子，我不知道如何將其擴展到多個級別

例子

這是說明問題的範例 DDL 和 DML

• lvl_one - 最頂層、根、父表
• lvl_two - lvl_one (1:M) 的子表
• lvl_three - lvl_two 的子表 (1:M)

設置

--DROP TABLE IF EXISTS lvl_one,lvl_two,lvl_three CASCADE;
CREATE TABLE IF NOT EXISTS public.lvl_one (
   id      bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
   name    text,
   CONSTRAINT lvl_one_pk PRIMARY KEY (id)
);
CREATE TABLE IF NOT EXISTS public.lvl_two (
   id          bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
   lvl_one_id  bigint NOT NULL,    
   name        text,
   CONSTRAINT lvl_two_pk PRIMARY KEY (id),
   CONSTRAINT lvl_two_lvl_one_id_fk FOREIGN KEY (lvl_one_id)
       REFERENCES public.lvl_one (id) 
);
CREATE TABLE IF NOT EXISTS public.lvl_three (
   id          bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
   lvl_two_id  bigint NOT NULL,    
   name        text,
   CONSTRAINT lvl_three_pk PRIMARY KEY (id),
   CONSTRAINT lvl_three_lvl_two_id_fk FOREIGN KEY (lvl_two_id)
       REFERENCES public.lvl_two (id) 
);

初始數據

-- initial data
INSERT INTO lvl_one(name)               VALUES ('Honda'),   ('Ford'),       ('Toyota');
INSERT INTO lvl_two(lvl_one_id, name)   VALUES (1,'Civic'), (1,'Passport'), (3,'Prius');
INSERT INTO lvl_three(lvl_two_id, name) VALUES (1,'door'),  (1,'window'),   (3,'trunk');


SELECT * FROM lvl_one ORDER BY id;
-- id, name
--  1, "Honda"
--  2. "Ford"
--  3, "Toyota"

SELECT * FROM lvl_two ORDER BY id;
-- id, lvl_one_id, name
--  1, 1,   "Civic"
--  2, 1,   "Passport"
--  3, 3,   "Prius"

SELECT * FROM lvl_three ORDER BY id;
-- id, lvl_two_id,  name
--  1, 1,   "door"
--  2, 1,   "window"
--  3, 3,   "trunk"

SELECT 
 one.id AS one_id, one.name AS one_name
, two.id AS two_id, two.name AS two_name
, three.id AS three_id, three.name AS three_name
FROM lvl_one AS one 
LEFT OUTER JOIN lvl_two AS two ON one.id = two.lvl_one_id
LEFT OUTER JOIN lvl_three AS three ON two.id = three.lvl_two_id
ORDER BY one.id, two.id, three.id;
--1 "Honda"     1       "Civic"     1       "door"
--1 "Honda"     1       "Civic"     2       "window"
--1 "Honda"     2       "Passport"  NULL    NULL        
--2 "Ford"      NULL    NULL        NULL    NULL            
--3 "Toyota"    3       "Prius"     3       "trunk"

解決方案（添加額外的列）

ALTER TABLE lvl_one   ADD COLUMN copied_from_id bigint;
ALTER TABLE lvl_two   ADD COLUMN copied_from_id bigint;
ALTER TABLE lvl_three ADD COLUMN copied_from_id bigint;


-- copy row id=1 from lvl_one and all its child tables
WITH source_one AS (
   SELECT id,name 
   FROM lvl_one 
   WHERE id=1
)
, copy_one AS (
   INSERT INTO lvl_one(name,copied_from_id)
   SELECT name,id AS copied_from_id
   FROM source_one
   RETURNING id AS new_one_id, copied_from_id
)
, copy_two AS (
   INSERT INTO lvl_two(lvl_one_id,name,copied_from_id)
   SELECT new_one_id, lvl_two.name,lvl_two.id AS copied_from_id
   FROM copy_one 
   INNER JOIN lvl_one ON lvl_one.id = copy_one.copied_from_id
   INNER JOIN lvl_two ON lvl_two.lvl_one_id = lvl_one.id
   RETURNING id AS new_two_id, copied_from_id
)
, copy_three AS (
   INSERT INTO lvl_three(lvl_two_id,name,copied_from_id)
   SELECT new_two_id, lvl_three.name, lvl_three.id AS copied_from_id
   FROM copy_two
   INNER JOIN lvl_two ON lvl_two.id = copy_two.copied_from_id
   INNER JOIN  lvl_three ON lvl_three.lvl_two_id = lvl_two.id
   RETURNING id AS new_three_id, copied_from_id
)
SELECT * FROM copy_one, copy_two, copy_three;

複製的預期結果lvl_one.id=1

由於“複製”行，將在所有 3 個表中創建以下行lvl_one.id=1。

-- lvl_one
-- 4,Honda,1

-- lvl_two
--4,4,Civic,1
--5,4,Passport,2

-- lvl_three
--4,4,door,1
--5,4,window,2

不幸的是，RETURNINGan 的子句INSERT只能與插入行中的列一起使用。由FROM子句添加的列在那裡是不可見的。

看：

• 返回導致錯誤：缺少表的 FROM 子句條目

為了解決這個限制，我建議SELECT在每個之前INSERT，並提前生成預期的新序列號nextval()。然後，您將每個舊 ID 和新 ID 放在同一行中以進行必要的連接。

這種方法的一個額外的小問題是您有IDENTITY帶有GENERATE ALWAYS. 所以無論如何我們都需要OVERRIDING SYSTEM VALUE寫入INSERT這些列。（或者您使用創建IDENTITY列GENERATED BY DEFAULT）：

WITH ins1 AS (
  INSERT INTO lvl_one(name)
  SELECT name
  FROM   lvl_one
  WHERE  id = 1  --  $1 here
  RETURNING id AS new_parent_id, name   -- just the one
  )
, sel2 AS (
  SELECT ins1.new_parent_id, t2.id, t2.name, nextval(pg_get_serial_sequence('lvl_two', 'id')) AS new_id
  FROM   ins1
  JOIN   lvl_two t2 ON t2.lvl_one_id = 1   --  and $1 here
  )
, ins2 AS (
  INSERT INTO lvl_two(id, lvl_one_id, name) OVERRIDING SYSTEM VALUE 
  SELECT new_id, new_parent_id, name
  FROM   sel2
  )
, sel3 AS (
  SELECT sel2.new_id AS new_parent_id, t3.id, t3.name, nextval(pg_get_serial_sequence('lvl_three', 'id')) AS new_id
  FROM   sel2
  JOIN   lvl_three t3 ON t3.lvl_two_id = sel2.id  -- old parent ID
  )
, ins3 AS (
  INSERT INTO lvl_three(id, lvl_two_id, name) OVERRIDING SYSTEM VALUE 
  SELECT new_id, new_parent_id, name
  FROM   sel3
  )
SELECT ins1.new_parent_id AS lvl1_id, ins1.name AS lvl1_name
    , sel2.new_id AS lvl2_id, sel2.name AS lvl2_name
    , sel3.new_id AS lvl3_id, sel3.name AS lvl3_name
FROM   ins1 
LEFT   JOIN sel2 USING (new_parent_id)
LEFT   JOIN sel3 ON sel3.new_parent_id = sel2.new_id
ORDER  BY lvl1_id, lvl2_id, lvl3_id;

db<>在這裡擺弄

性能應該非常相似。主要好處是我們不需要額外的表列。

第一個INSERT很簡單，因為根據定義它只能影響單行，所以我沒有在SELECT那裡添加另一個。以下步驟遵循相同的模式，並且可以根據需要降低任意多個級別。

另請注意，SELECT原始解決方案中的外部會產生不正確的結果（不影響實際插入的行）：

...
SELECT * FROM copy_one, copy_two, copy_three;

CROSS JOIN在表之間將合併不應合併的行並排除不應排除的行。

引用自：https://dba.stackexchange.com/questions/292199