插入cte后执行删除cte

Question

我有张桌子：

CREATE TABLE product
(
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    from_id bigint NOT NULL,
    to_id   bigint NOT NULL,
    comments text NOT NULL,
    data    jsonb  NOT NULL
);

CREATE UNIQUE INDEX product_unique_idx ON product(from_id, to_id, comments);

有以下数据：

insert into product(from_id, to_id, comments, data) values
(1, 2, 'bla', '{}'),
(2, 3, 'bla', '{}'),
(1, 3, 'bla', '{}'),
(3, 2, 'bla', '{}'),
(2, 1, 'bla', '{}'),
(3, 1, 'bla', '{}');

现在，我想插入使用给定集合更新from_id & to_id的新记录，例如，将所有from_id & to_ids 1,2替换为3(也删除where from_id == to_id)，因为UNIQUE INDEX I通过insert执行，然后通过delete完成所有其他行：

with
   insert_stmt_to_id AS (
      insert into product
         (from_id, to_id, comments, data)
         (select from_id,3,comments,data from product
           where to_id in (1,2))
         ON CONFLICT (from_id, to_id, comments) DO NOTHING),
   insert_stmt_from_id AS (
      insert into product
         (from_id, to_id, comments, data)
         (select 3,to_id,comments,data from product
           where from_id in (1,2))
         ON CONFLICT (from_id, to_id, comments) DO NOTHING),
   delete_stmt AS (DELETE from product where to_id in (1,2) or from_id in (1,2) RETURNING *)
select * from delete_stmt 

但是在select * from product之后，我得到(在from+it\to_id中有1，2)：

​

​

怎么样？

Answer 1

所以我设法做到了：

with
   delete_duplicates as (
      delete from product where
         (from_id in (1,2) and to_id in (1,2)) or
         (from_id = 3 and to_id in (1,2)) or
         (from_id in (1,2) and to_id  = 3)
      RETURNING id),
   insert_stmt_from_id AS (
      insert into product
         (from_id, to_id, comments, data)
         (select 3,to_id,comments,data from product
           where from_id in (1,2) and not id in (select id from delete_duplicates))
       ON CONFLICT (from_id, to_id, comments) DO NOTHING RETURNING id),
   insert_stmt_to_id AS (
      insert into product
         (from_id, to_id, comments, data)
         (select from_id,3,comments,data from product
           where to_id in (1,2) and not id in (select id from delete_duplicates))
       ON CONFLICT (from_id, to_id, comments) DO NOTHING RETURNING id),
   delete_leftovers_stmt AS (DELETE from product where from_id in (1,2) or to_id in (1,2) RETURNING id)
select id from delete_duplicates union select id from delete_leftovers_stmt;