PostgreSQL-根据从另一个表中的选择插入行，并使用新插入的行在该表中更新FK

Question

我正在两个表之间进行数据迁移（拆分出一个相关的表）。现有表为reminders，它具有一start列和一个dateset_id指向新dateset表的新添加的列，该表也具有一start列。对于每一行reminders，我要INSERT一个新的行dateset与start复制价值，并UPDATE在相应的行reminders与新插入的datesetID。

这是我尝试过的SQL：

WITH inserted_datesets AS (
  INSERT INTO dateset (start)
  SELECT start FROM reminder
  RETURNING reminder.id AS reminder_id, id AS dateset_id
)
UPDATE reminder
SET dateset_id = ids.dateset_id
FROM inserted_datesets AS ids
WHERE reminder.id = ids.reminder_id

我收到一个错误missing FROM-clause entry for table "reminder"，因为我reminder.id在该RETURNING子句中包括了该列，但实际上没有为插入选择它。这是有道理的，但我不知道如何修改查询以执行所需的操作。我缺少一种完全不同的方法吗？

Answer 1

有几种解决问题的方法。

1.临时添加一列

正如其他人提到的那样，简单的方法是将列临时添加reminder_id到中dateset。原装填充它IDs从reminder表。使用它reminder与dateset表连接。删除临时列。

2.何时启动是唯一的

如果该start列的值是唯一的，则可以通过将remindertable与该dateset列上的表连接起来而无需额外的start列。

INSERT INTO dateset (start)
SELECT start FROM reminder;

WITH
CTE_Joined
AS
(
    SELECT
        reminder.id AS reminder_id
        ,reminder.dateset_id AS old_dateset_id
        ,dateset.id AS new_dateset_id
    FROM
        reminder
        INNER JOIN dateset ON dateset.start = reminder.start
)
UPDATE CTE_Joined
SET old_dateset_id = new_dateset_id
;

3.启动时不是唯一的

即使在这种情况下，也可以在没有临时列的情况下进行操作。主要思想如下。让我们看一下这个例子：

我们有两个reminder具有相同start值和ID 3和7的行：

reminder
id    start         dateset_id
3     2015-01-01    NULL
7     2015-01-01    NULL

将它们插入之后dateset，将生成新的ID，例如1和2：

dateset
id    start
1     2015-01-01
2     2015-01-01

我们如何链接这两行并不重要。最终结果可能是

reminder
id    start         dateset_id
3     2015-01-01    1
7     2015-01-01    2

或者

reminder
id    start         dateset_id
3     2015-01-01    2
7     2015-01-01    1

这两个变体都是正确的。这带给我们以下解决方案。

只需首先插入所有行。

INSERT INTO dateset (start)
SELECT start FROM reminder;

在start知道列不是唯一的情况下匹配/联接列上的两个表。通过添加ROW_NUMBER和连接两列来“使它变得唯一”
。可以使查询更短，但是我明确说明了每个步骤：

WITH
CTE_reminder_rn
AS
(
    SELECT
        id
        ,start
        ,dateset_id
        ,ROW_NUMBER() OVER (PARTITION BY start ORDER BY id) AS rn
    FROM reminder
)
,CTE_dateset_rn
AS
(
    SELECT
        id
        ,start
        ,ROW_NUMBER() OVER (PARTITION BY start ORDER BY id) AS rn
    FROM dateset
)
,CTE_Joined
AS
(
    SELECT
        CTE_reminder_rn.id AS reminder_id
        ,CTE_reminder_rn.dateset_id AS old_dateset_id
        ,CTE_dateset_rn.id AS new_dateset_id
    FROM
        CTE_reminder_rn
        INNER JOIN CTE_dateset_rn ON 
            CTE_dateset_rn.start = CTE_reminder_rn.start AND
            CTE_dateset_rn.rn = CTE_reminder_rn.rn
)
UPDATE CTE_Joined
SET old_dateset_id = new_dateset_id
;

我希望可以从代码中清楚地知道它的作用，尤其是当您将它与没有的简单版本进行比较时ROW_NUMBER。显然，即使start是唯一的，复杂的解决方案也可以使用，但效率却不如简单的解决方案。

此解决方案假定dateset在此过程之前为空。