PostgreSQL：复制表行并获取新旧匹配信息

jei2mxaa 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(136)

这里我们有一个特定的表：

CREATE TABLE mytbl (
  id int PRIMARY KEY generated by default as identity,
  col1 int,
  col2 text, ...
);

我们需要复制表的部分行，并获取有关新旧ID对应关系的信息（以便从其他表复制相关数据）。
我试过这样一个查询：

insert into mytbl (col1, col2)
    select col1, col2
        from mytbl old
        where col1 = 999 -- some condition
    returning 
        old.id as old_id,
        id as new_id;

但是这个查询会产生错误：ERROR: missing FROM-clause entry for table "old"
有没有一种方法可以编写一个类似的查询而不会出错？

我已经想到的是 *
在循环中一次复制一行--显然我不得不这样做，除非找到更简单的方法。
alter table mytbl add column old_id（外键引用mytbl），然后是insert into mytbl (old_id, col1, col2) select id, col1, col2 ... returning id as new_id, old_id-但实际上，我们不需要存储这些信息，因为只有在创建副本时才需要它。
不返回的INSERT SELECT;然后SELECT old.id as old_id, new.id as new_id FROM mytbl new JOIN mytbl old ON（* 智能键的一些连接条件 *）-在我的情况下是相当困难的。

postgresql

来源：https://stackoverflow.com/questions/76518722/postgresql-copy-the-table-rows-and-get-the-old-new-matching-information

1条答案

按热度按时间

yvt65v4c1#

你可以（但可能不应该）使用rely on the order of inserted rows来匹配所选内容和RETURNING子句：

WITH selection AS (
    SELECT id, col1, col2, row_number() OVER (ORDER BY id)
    FROM mytbl
    WHERE col1 = 999 -- some condition
    ORDER BY id
), inserted AS (
    INSERT INTO mytbl (col1, col2)
    SELECT col1, col2
    FROM selection
    ORDER BY selection.id
    RETURNING id
)
SELECT s.id AS old_id, ins.id AS new_id
FROM (SELECT inserted.id, row_number() OVER (ORDER BY inserted.id) FROM inserted) AS ins
JOIN selection USING (row_number);

一个可能更好的方法（也是works nicely when you need to copy in multiple mutually-dependant tables）是在插入之前使用the sequence that is underlying the identity column生成新的id：

WITH selection AS (
    SELECT nextval(pg_get_serial_sequence('mytbl', 'id')) AS new_id, id AS old_id, col1, col2
    FROM mytbl
    WHERE col1 = 999 -- some condition
), inserted AS (
    INSERT INTO mytbl(id, col1, col2)
    OVERRIDING SYSTEM VALUE -- necessary when using identity columns!
    SELECT new_id, col1, col2
    FROM selection
)
SELECT old_id, new_id
FROM selection;

但是，这确实需要对基础序列的USAGE权限，即使角色通常可以使用标识列，默认情况下也可能不会授予该权限。

赞(0）回复(0）举报 2023-06-22

我来回答

PostgreSQL：复制表行并获取新旧匹配信息

1条答案

相关问题

热门标签

最新问答