删除/插入行的 SQL CTE 语法

Question

从表中删除，然后插入到同一个表并返回插入的值的 CTE 语法是什么？

在 2 小时的 sleep 中运行，但看起来有些不对劲(除了这不会执行的事实):

WITH delete_rows AS (
   DELETE FROM <some_table> WHERE id = <id_value>
   RETURNING *
)
SELECT * FROM delete_rows
UNION
(
   INSERT INTO <some_table> ( id, text_field )
      VALUES ( <id_value>, '<text_field_value>' )
      RETURNING *
)

预期的行为是首先清除某个 ID 的所有记录，然后插入相同 ID 的记录(故意不是更新插入)并返回那些插入的记录(而不是删除)。

Answer 1

您的问题更新明确表示您不能在单个语句中执行此操作。

打包到同一语句的 CTE 中，两个操作(INSERT 和 DELETE)将看到相同的表快照并几乎同时执行。即 INSERT仍然会看到您认为已经删除的所有行。 The manual:

All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables.

您可以将它们作为两个独立的语句包装到同一个事务中——这似乎也不是绝对必要的，但它会允许整个操作自动成功/失败:

BEGIN;

DELETE FROM <some_table> WHERE id = <id_value>;

INSERT INTO <some_table> (id, text_field)
VALUES ( <id_value>, '<text_field_value>')
RETURNING *;

COMMIT;

现在，INSERT可以看到DELETE的结果.