如何在 PostgreSQL 中使用 INSERT … SELECT 獲取最後插入的行？

我正在嘗試在 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 中遺漏了一些明顯的東西讓我這樣做？那將是非常有利的。

似乎您只需要最後插入的最大值（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 的範圍。

如果您不想通過變數重新註入邊界，則可以將其保留在專用的單行表中。

引用自：https://dba.stackexchange.com/questions/242757