主页 / dba / 问题 / 242757
Accepted
gertvdijk
Asked: 2019-07-13 05:53:32 +0800 CST

如何在 PostgreSQL 中使用 INSERT ... SELECT 获取最后插入的行？

  • 772

我正在尝试在 PostgreSQL 12-beta2 中以特定顺序将数据从一个表批量复制到另一个表。该表未使用序列，但包含复合唯一主键 ( user_id, object_id)。

为了确定下一批从哪里开始，我想从最后插入的行 ( WHERE user_id >= last_user_id AND object_id > last_object_id) 开始。

从这个开始：

INSERT INTO dest_table
SELECT (user_id, object_id, object_type, colN, ...) 
FROM source_table 
ORDER BY user_id, colN, object_id  -- this is indexed
LIMIT 1000  -- batch size
RETURNING user_id, object_id;

... 返回一个包含 1000 个元组的表。我想从中获取最后插入的元组。

我试图围绕它做一个 SELECT ，如下所示：

SELECT user_id, object_id FROM (
    INSERT INTO dest_table
    SELECT (user_id, object_id, object_type, colN, ...) 
    FROM source_table 
    ORDER BY user_id, colN, object_id  -- this is indexed
    LIMIT 1000  -- batch size
    RETURNING user_id, object_id
)
ORDER BY user_id DESC, colN DESC, object_id DESC
LIMIT 1
RETURNING user_id, object_id;

但这会返回语法错误：

ERROR:  syntax error at or near "INTO"
LINE 2: INSERT INTO dest_table
               ^

我也尝试RETURNING ... INTO variable 过这里描述的，但这也失败了：

ERROR:  syntax error at or near "INTO"
LINE 23: RETURNING user_id, object_id INTO my_variable;
                                      ^

我是否需要为此创建一个函数（例如 plpgsql），或者我是否在普通 SQL 中遗漏了一些明显的东西让我这样做？那将是非常有利的。

postgresql insert

2 个回答

  1. Best Answer

    似乎您只需要最后插入的最大值（user_id，object_id）作为一对。第一个 INSERT 可能是：

    WITH inserted_rows as (
INSERT INTO dest_table
SELECT user_id, object_id, object_type, colN, ...
FROM source_table 
ORDER BY user_id, colN, object_id  -- this is indexed
LIMIT 1000  -- batch size
RETURNING user_id, object_id
)
SELECT row(user_id,object_id) FROM inserted_rows
 ORDER BY 1 DESC LIMIT 1;

    为方便起见，此表格使用一行来指代(user_id,object_id)这对夫妇。根据您的情况需要从左到右比较行元素，以使用这两个值作为边界。请参阅 文档中的行构造函数比较。

    使用此解决方案，随后的 INSERT 将注入最后一个先前插入返回的值。这样可以避免在dest_table.

    WITH inserted_rows as (
INSERT INTO dest_table
SELECT user_id, object_id, object_type, colN, ...
FROM source_table
  WHERE row(user_id,object_id) > row(...inject the result of the previous query here...)
ORDER BY user_id, colN, object_id
LIMIT 1000
RETURNING user_id, object_id
)
SELECT row(user_id,object_id) FROM inserted_rows
ORDER BY 1 DESC LIMIT 1;

    我是否需要为此创建一个函数（例如 plpgsql），或者我是否在普通 SQL 中遗漏了一些明显的东西让我这样做？那将是非常有利的。

    在纯 SQL 中，您将在一个步骤中执行单个 INSERT，而无需任何批处理。由于您想将其分成批次，因此必须有一些循环驱动这些带有停止条件的 INSERT，并且此过程逻辑超出了纯 SQL 的范围。

    如果您不想通过变量重新注入边界，则可以将其保留在专用的单行表中。

    • 1

  2. 如果除了您自己之外没有其他人正在写信dest_table，请在所有后续批次的子查询中获取最后插入的 ID，例如：

    INSERT INTO dest_table
SELECT (user_id, object_id, object_type, colN, ...) 
FROM source_table
WHERE
    user_id >= (
        SELECT user_id
        FROM dest_table
        ORDER BY user_id DESC, colN DESC, object_id DESC
        LIMIT 1)
    )
    AND object_id > (
        SELECT event_id
        FROM dest_table
        ORDER BY user_id DESC, colN DESC, object_id DESC
        LIMIT 1)
    )
ORDER BY user_id, colN, object_id
LIMIT 1000;
    • 0

相关问题