SQL CTE语法用于删除/插入行。

Question

如何使用CTE语法从表中删除数据并将其插入到同一表中，同时返回插入的值?

只睡了2个小时，但代码似乎有问题（除了这个事实，即它无法执行）：

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的记录（故意不进行更新操作），并返回这些已插入的记录（而非被删除的记录）。

- vol7ron

---

插入和删除似乎没有联系，但您想要从两个操作中返回所有行？ - Erwin Brandstetter

@ErwinBrandstetter对于delete真的不太在意，但不确定这是否是正确的语法，打算在能按顺序执行操作之后再处理这个问题。 - vol7ron

好的，最后一点可能会有问题。请更新您的问题，明确您期望两者如何“按顺序”执行。 - Erwin Brandstetter

谢谢Erwin，已更新；你是正确的。 - vol7ron

Answer 1

您的问题更新表明您无法在单个语句中完成此操作。

将INSERT和DELETE操作打包到同一语句的CTE中，两者将看到表的相同快照并几乎同时执行。也就是说，INSERT仍会看到您认为已删除的所有行。 手册:

所有语句都使用相同的快照进行执行（请参见第13章），因此它们无法“看到”目标表上彼此的影响。

您可以将它们封装为两个独立的语句放入同一事务中 - 这似乎也不是绝对必要的，但它将允许整个操作原子地成功/失败：

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 的结果。

- Erwin Brandstetter

---

@vol7ron：嗯，UNION从左到右进行评估（请参见此处的最后一章：https://dev59.com/D4vda4cB1Zd3GeqPedFL#30813090）。您还可以引用下一个CTE中从`RETURNING`子句获取的内容以强制执行评估顺序。但是，CTE始终看到底层表的相同快照。 - Erwin Brandstetter

所以可能存在与另一个查询的竞争条件，没有表级别或约束保留，但在查询内部，它将按预期顺序依次执行，对吗？只是想确保即使从左到右，它也不会并行操作或跳过，直到一个操作完成。 - vol7ron

@vol7ron：不确定你的意思。如果在“DELETE”和“INSERT”之间存在重叠，并且存在唯一约束，则无法在单个语句中使用两个数据修改CTE来完成此操作。您需要首先使用单独的语句进行删除，就像我的答案一样。 - Erwin Brandstetter

我怀疑 union 是解决方法，并且确实启用了该行为；它们可能是不同的快照，或者至少相同的快照按从左到右的顺序被处理（这就是为什么在编辑之前您的查询有效的原因）。 - vol7ron

好的，第一个查询目的不正确。好奇的读者可以在编辑历史中查看。 - Erwin Brandstetter

Answer 2

CREATE TABLE test_table (value TEXT UNIQUE);
INSERT INTO test_table SELECT 'value 1';
INSERT INTO test_table SELECT 'value 2';

WITH delete_row AS (DELETE FROM test_table WHERE value='value 2' RETURNING 0)
  INSERT INTO test_table
    SELECT DISTINCT 'value 2' 
    FROM (SELECT 'dummy') dummy
    LEFT OUTER JOIN delete_row ON TRUE
    RETURNING *;

上述查询处理了DELETE删除0/1/一些行的情况。

- skif1979

Answer 3

深入探讨skif1979的“DelSert”CTE方法，“Logged DelSert:”

-- setups
DROP TABLE IF EXISTS _zx_t1 ;

CREATE TEMP TABLE 
  IF NOT EXISTS 
     _zx_t1 
     ( id bigint
     , fld2 bigint
     , UNIQUE (id)
     );
-- unique records
INSERT INTO _zx_t1 SELECT 1, 99;
INSERT INTO _zx_t1 SELECT 2, 98;


WITH 
  _cte_del_row AS 
   (   DELETE 
       FROM _zx_t1 
       WHERE id = 2 
     RETURNING id as _b4_id, fld2 as _b4_fld2 -- returns complete deleted row
   )
 , _cte_delsert AS
     (  INSERT 
       INTO _zx_t1 
       SELECT DISTINCT 
          _cte_del_row._b4_id
        , _cte_del_row._b4_fld2 + 1 
        from (SELECT null::integer AS _zunk) _zunk  -- skif1979's trick here
             LEFT OUTER JOIN _cte_del_row         -- clever LOJ magic  
             ON TRUE                              -- LOJ cartesian product
        RETURNING id as _aft_id , fld2 as _aft_fld2 -- return newly "delserted" rows
       )
  SELECT * -- returns before & after snapshots from CTE's
  FROM 
   _cte_del_row
   , _cte_delsert ; 

 RESULT: 
           _b4_id | _b4_fld2 | _aft_id | _aft_fld2 
          --------+----------+---------+-----------
                2 |      209 |       2 |       210

据我所知，所有这些都在工作单元内线性发生，类似于日志更新。

• 适用于

  • 子记录
  • 没有外键的模式
  • 或具有级联删除的外键

• 不适用于

  • 具有外键但没有级联删除的父记录

- Faking Leer

Answer 4

一个相关（并且在我看来更好）的答案，类似于“已记录的DelSert”，是一个已记录的“SelUp”：

    -- setups
    DROP TABLE IF EXISTS _zx_t1 ;

    CREATE TEMP TABLE 
      IF NOT EXISTS 
         _zx_t1 
         ( id bigint
         , fld2 bigint
         , UNIQUE (id)
         );
    -- unique records
    INSERT INTO _zx_t1 SELECT 1, 99;
    INSERT INTO _zx_t1 SELECT 2, 98;


    WITH 
      _cte_sel_row AS 
       (   SELECT                 -- start unit of work with read
              id as _b4_id        -- fields need to be aliased 
             ,fld2 as _b4_fld2    -- to prevent ambiguous column errors
           FROM _zx_t1 
           WHERE id = 2
           FOR UPDATE 
       )
     , _cte_sel_up_ret AS           -- we're in the same UOW
       (  UPDATE _zx_t1             -- actual table
           SET fld2 = _b4_fld2 + 1  -- some actual work
          FROM  _cte_sel_row    
            WHERE id = _b4_id
               AND fld2 < _b4_fld2 + 1  -- gratuitous but illustrates the point 
          RETURNING id as _aft_id, fld2 as _aft_fld2
         ) 
    SELECT  
          _cte_sel_row._b4_id
         ,_cte_sel_row._b4_fld2         -- before
         ,_cte_sel_up_ret._aft_id  
         ,_cte_sel_up_ret._aft_fld2     -- after
       FROM _cte_sel_up_ret  
          INNER JOIN _cte_sel_row  
           ON TRUE AND _cte_sel_row._b4_id = _cte_sel_up_ret._aft_id
    ;

 RESULT: 
           _b4_id | _b4_fld2 | _aft_id | _aft_fld2 
          --------+----------+---------+-----------
                2 |      209 |       2 |       210

参见： https://rob.conery.io/2018/08/13/transactional-data-operations-in-postgresql-using-common-table-expressions/

- Faking Leer

Answer 5

UNION接受SELECT语句，但不接受INSERT语句。

假设创建了一个非唯一id的表：

CREATE TABLE some_table (id int, text_field text);
INSERT INTO some_table VALUES (1, 'a'),(1, 'b');

所以初始请求可以看起来像这样：

WITH delete_rows AS (DELETE FROM some_table WHERE id = 1 RETURNING *),
    insert_rows AS (
      INSERT INTO some_table (id, text_field) VALUES (1, 'c'),(1, 'd'),(1, 'e') RETURNING *
    )
(SELECT 'deleted', * FROM delete_rows)
UNION ALL
(SELECT 'inserted', * FROM insert_rows);

?column? | id | text_field 
----------+----+------------
 deleted  |  1 | a
 deleted  |  1 | b
 inserted |  1 | c
 inserted |  1 | d
 inserted |  1 | e
(5 rows)

如果不需要删除的行：

WITH delete_rows AS (DELETE FROM some_table WHERE id = 1),
    insert_rows AS (
      INSERT INTO some_table (id, text_field) VALUES (1, 'c'),(1, 'd'),(1, 'e') RETURNING *
    )
SELECT * FROM insert_rows;

或者只是：

WITH delete_rows AS (DELETE FROM some_table WHERE id = 1)
    INSERT INTO some_table (id, text_field) VALUES (1, 'c'),(1, 'd'),(1, 'e') RETURNING *;

 id | text_field 
----+------------
  1 | c
  1 | d
  1 | e
(3 rows)

- guest