sql - 如何在 Postgres 中基于 INSERT RETURNING id 进行 SQL 更新？

Question

我正在使用 Postgres。我有一张旧 table 和一张新 table 。旧表中的记录需要在新表中有相关​​记录。新表记录将有效地充当新表中记录的父级。

我正在尝试编写一个迁移，其中旧表中的每个“子项”都将在新表中具有“父项”。

OLD TABLE(我添加了一个外键，希望在迁移过程中填充它)

 id | name | new_id (FK)
----+------+-------------
  1 | 1st  | 
  2 | 2nd  |

新表

 id | name 
----+------
    |

我需要做以下事情:

• SELECT 旧表中的所有记录
• INSERT 为每条旧记录和RETURNING id
将一条记录插入新表
• UPDATE 旧记录的外键值与 RETURNING id

有没有办法使用集合查询来完成此操作？或者我是否需要开始研究 Postgres 特定的东西，比如 LOOPs？

Answer 1

您可以按如下方式使用可写 cte:

--open transaction
BEGIN;

--avoid concurrent writes
LOCK old_table IN ROW SHARE MODE;
LOCK new_table IN ROW SHARE MODE;

--create a sequence to populate new_table.id
CREATE SEQUENCE new_table_seq;

--using a writable cte to do the dirty job
WITH old_table_cte AS (
    UPDATE old_table SET new_id = nextval('new_table_seq') RETURNING *
)
INSERT INTO new_table SELECT new_id, name FROM old_table_cte;

--Create a proper FK
ALTER TABLE old_table ADD CONSTRAINT old_table_new_table_fk FOREIGN KEY (new_id) REFERENCES new_table (id);

--end transaction
COMMIT;

这种方法有一些好处:

• 按照您的要求利用 RETURNING，但在 update 步骤中。避免不必要的额外的选择；
• 并发安全；
• 推迟 FK 创建将导致更快的脚本；
• 使用纯SQL解决方案，无需使用过程语言；
• 避免在同一步骤中读取和写入 old_table。