对于每个选定的行，是否插入另一个行?

Question

我有一个表A。它有列id、type、relatedId、another1、another2。列type的值可以1, 2 or 3。 我需要的是，对于 TableA 中的每一行，其中type = 1，在同一表中插入另一行，并使用新插入的行id更新原始行(第relatedId列)。此外，新插入行中某些列的值应从原始行复制。

所以对于当前状态：

id|type|relatedId|another1
10| 1  |null|"some text"
11| 2  |null|"somthing"
12| 1  |null|"somthing else"

结果应如下所示：

id|type|relatedId|another1
10| 1  |13  |"some text"      - now has relationship to 13
11| 2  |null|"somthing"
12| 1  |14  |"somthing else"  - now has relationship to 13
13| 3  |null|"some text"      - inserted, "another1" is copied from 10
14| 3  |null|"somthing else"  - inserted, "another1" is copied from 12

Answer 1

假设文本是唯一的，您可以这样做：

演示：数据库<>小提琴

WITH ins AS (
INSERT INTO tablea(type, related_id, another1)
SELECT 3, null, another1
FROM tablea
WHERE type = 1
RETURNING id, another1
)
UPDATE tablea t
SET related_id = s.id
FROM (
SELECT * FROM ins
) s
WHERE s.another1 = t.another1 AND t.type = 1

WITH 子句允许按顺序执行两个单独的语句。因此，首先插入新数据。使用新生成的 ID，您可以在之后更新旧数据。由于您必须匹配原始数据，因此文本作为标识符很有用。

这仅在您不必使用 (1， 'something') 进行数据集时有效。那么就很难确定两份记录中的哪一条是每个副本的原件。

---

另一种方法是将 type1-id 也存储在新的 type3 列中。如果这对您来说没问题，您可以这样做：

演示：数据库<>小提琴

WITH ins AS (
INSERT INTO tablea(type, related_id, another1)
SELECT 3, id, another1
FROM tablea
WHERE type = 1
RETURNING id, related_id, another1
)
UPDATE tablea t
SET related_id = s.id
FROM (
SELECT * FROM ins
) s
WHERE s.related_id = t.id

这会将原始 type1-id 存储在新 type1-id 的related_id列中。因此，在每种情况下，都可以在此值上找到原始 id。

遗憾的是，您不能在另一个 WITH 子句中空掉这些列，因为 WITH 子句仅适用于现有数据。此时，查询本身尚未完成。因此，新记录在物理上并不存在。

---

这个可以工作...

演示：数据库<>小提琴

WITH to_be_copied AS (
SELECT id, another1
FROM tablea
WHERE type = 1
), ins AS (
INSERT INTO tablea(type, related_id, another1)
SELECT 3, null, another1
FROM to_be_copied
ORDER BY id                         -- 1
RETURNING id, another1
)
UPDATE tablea t
SET related_id = s.type3_id
FROM (
SELECT 
* 
FROM 
(SELECT id as type1_id, row_number() OVER (ORDER BY id) FROM to_be_copied) tbc 
JOIN 
(SELECT id as type3_id, row_number() OVER (ORDER BY id) FROM ins) i 
ON tbc.row_number = i.row_number
) s
WHERE t.id = s.type1_id

此解决方案假定 (1) 处的给定顺序可确保新记录的插入顺序。事实上，我不太确定。但如果是这样：首先查询所有 type1 记录。之后有复制(以相同的顺序！之后，将采用旧记录和新记录 ID。row_number() 窗口函数向记录添加连续的行计数。因此，如果两个数据集具有相同的顺序，则旧 ID 应获得与其相应的新 ID 相同的行号。在这种情况下，可以进行识别。对于这个小例子，这有效...

-->编辑：这似乎在说：是的，自 Postgres 9.6 https://stackoverflow.com/a/50822258/3984221 以来，订单将被保留

Answer 2

根据这个问题，Postgres 保留了从 9.6 开始具有明确ORDER BY的SELECT插入的行顺序。我们可以使用它来将插入的行与它们来自的行连接起来 使用row_number().

WITH
"cte1"
AS
(
SELECT "id",
3 "type",
"related_id",
"another1",
row_number() OVER (ORDER BY "id") "rn"
FROM "tablea"
WHERE "type" = 1
),
"cte2"
AS
(
INSERT INTO "tablea"
("type",
"another1")
SELECT "type",
"another1"
FROM "cte1"
ORDER BY "id"
RETURNING "id"
),
"cte3"
AS
(
SELECT "id",
row_number() OVER (ORDER BY "id") "rn"
FROM "cte2"
)
UPDATE "tablea"
SET "related_id" = "cte3"."id"
FROM "cte1"
INNER JOIN "cte3"
ON "cte3"."rn" = "cte1"."rn"
WHERE "cte1"."id" = "tablea"."id";

在第一个 CTE 中，我们得到所有行，这些行应该与按其 ID 排序的row_number()一起插入。在第二个中，我们通过从第一个 CTE 中选择按 ID 显式排序来插入它们。我们在第二个 CTE 中返回插入的 ID，以便我们可以在第三个 CTE 中选择它，再次添加按 ID 排序的row_number()。现在，我们可以通过行号加入第一个和第三个 CTE，以获取原始 ID 和新插入的 ID 对。在此基础上，我们可以更新表设置相关 ID。

数据库<>小提琴